Note: Index Organized
Tables are tables that, unlike heap tables, are organized like B*Tree indexes.
Note: From Jonathan Lewis on secondary indexes
I think secondary indexes on IOTs need some careful testing. It's probably not an area that many people have used in a high-stress environment.
There are two main issues:
The primary key is used in the secondary index instead of a rowid so for large primary keys, the index would be bigger than the equivalent index on a simple table.
The secondary index holds a 'guess' block address for the row it points to so that a query can go to the right block more cheaply. But if the row has moved (e.g. leaf block split) then the guess is wrong and is a cost, not a benefit. But this won't be a problem if your application is always adding data at the 'right-hand' edge of the index.
Depending of version, there are various features and limitations on what you can do with secondary indexes that you will have to trade, balance and test, if you go down that path.
(And yes, the key compression could well have a very similar benefit to the varray idea, whilst avoiding the overhead of 'object
unpickling')
Data Dictionary Objects
dba_tables
all_tables
user_tables
dba_tab_cols
all_tab_cols
user_tab_cols
Create
Simple Create IOT
CREATE TABLE <table_name> (
<column_name> <data type and precision>,
<column_name> <data type and precision>,
CONSTRAINT <constraint_name>
PRIMARY KEY (<primary key constraint columns>))
ORGANIZATION INDEX;
SELECT table_name, iot_name, iot_type
FROM user_tables;
DROP TABLE labor_hour;
SELECT object_name, original_name, type, related
FROM user_recyclebin;
FLASHBACK TABLE labor_hour TO BEFORE DROP;
SELECT object_name, object_type
FROM user_objects
ORDER BY 1,2;
ALTER INDEX "BIN==$0" RENAME TO
pk_labor_hour;
Index Compressed IOT
CREATE TABLE <table_name> (
<column_name> <data type and precision>,
<column_name> <data type and precision>,
CONSTRAINT <constraint_name>
PRIMARY KEY (<primary key constraint columns>))
ORGANIZATION INDEX
COMPRESS <number_of_columns>;
CREATE TABLE compressed_iot
(owner, object_id, object_name,
CONSTRAINT pk_compressed_iot
PRIMARY KEY(owner, object_id, object_name))
ORGANIZATION INDEX
COMPRESS 2 AS
SELECT owner, object_id, object_name
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
Complex IOT with Including Clause
CREATE TABLE <table_name> (
<column_name> <data type and precision>,
<column_name> <data type and precision>,
CONSTRAINT <constraint_name>
PRIMARY KEY (<primary key constraint columns>))
ORGANIZATION INDEX
INCLUDING <column_name>
OVERFLOW TABLESPACE <tablespace_name>;
Complex IOT with Including Clause And Partitioning
CREATE TABLE <table_name> (
<column_name> <data type and precision>,
<column_name> <data type and precision>,
CONSTRAINT <constraint_name>
PRIMARY KEY (<primary key constraint columns>))
ORGANIZATION INDEX
INCLUDING <column_name>
OVERFLOW TABLESPACE <tablespace_name>
PARTITION BY RANGE (<partitioning_column>)
(<partition definitions>);
--
DDL for the tablespaces required for this demo are
[here]
CREATE TABLE labor_hour (
WORK_DATE DATE,
EMPLOYEE_NO VARCHAR2(8),
SUMMIT_WORK_ORDER_NO VARCHAR2(7),
DASH
VARCHAR2(2),
CLASS_CODE VARCHAR2(6),
PAYCODE
VARCHAR2(2),
ASSIGNED_CREW_NUMBER VARCHAR2(5),
TRANSFER_CREW_NUMBER VARCHAR2(5),
REFERENCE_TYPE VARCHAR2(1),
REFERENCE_NUMBER VARCHAR2(10),
OVERTIME_CODE VARCHAR2(1),
SHIFT_DIFFERENTIAL VARCHAR2(1) NOT NULL,
HOURS
NUMBER(4,2) NOT NULL,
MOD_USER_ID VARCHAR2(30) DEFAULT USER,
MOD_USER_DATE DATE DEFAULT SYSDATE,
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no, summit_work_order_no, dash, class_code, paycode,
assigned_crew_number, transfer_crew_number, reference_type, reference_number, overtime_code, shift_differential))
ORGANIZATION INDEX
INCLUDING hours
OVERFLOW TABLESPACE uwdata
PARTITION BY RANGE (work_date) (
PARTITION yr06 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')) TABLESPACE
part1,
PARTITION yr07 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')) TABLESPACE
part2,
PARTITION yr08 VALUES LESS THAN (TO_DATE('01-JAN-2009', 'DD-MON-YYYY')) TABLESPACE
part3,
PARTITION yr99 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);
Mapping Table
Clause
Specify MAPPING TABLE to instruct Oracle to
create a mapping of local to physical ROWIDs and store them in a heap-organized table.
This mapping is needed in order to create a bitmap index on the index-organized table.
Oracle creates the mapping table in the same tablespace as its parent index-organized
table. You cannot query, perform DML operations on, or modify the storage characteristics
of the mapping table.
You cannot specify the mapping_table_clause for a partitioned index-organized table.
Free