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;
ALTERINDEX "BIN$jzohHP0LRqusV3X3jtaDRQ==$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>;
CREATETABLE 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]
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