Creating secondary Indexes on Index Organized Tables

• Syntax for Creating a Secondary Index
  The syntax for creating a secondary index on an index-organized table is exactly the same as the syntax for creating a normal index, as shown in the following diagram:
  

  

  
  

  CREATE INDEX	Required keywords.
  index_name	The name of the index you will create
  ON	Required keyword
  column_list	A list of columns in the index, separated by columns

1. They store logical rowids instead of physical rowids. This is necessary because the inherent movability of rows in a B-tree index results in the rows having no permanent physical addresses. If the physical location of a row changes, its logical rowid remains valid. One effect of this is that a table maintenance operation, such as ALTER TABLE ... MOVE, does not make the secondary index unusable.
2. The logical rowid also includes a physical guess which identifies the database block address at which the row is likely to be found. If the physical guess is correct, a secondary index scan would incur a single additional I/O once the secondary key is found. The performance would be similar to that of a secondary index-scan on an ordinary table.

• Creating a Secondary Index on an Index-Organized Table
  The following statement shows the creation of a secondary index on the docindex index-organized table where doc_id and token are the key columns:
  

  CREATE INDEX Doc_id_index on Docindex(Doc_id, Token);
  

  
  This secondary index allows the database to efficiently process a query, such as the following, the involves a predicate on doc_id:
  

  SELECT Token FROM Docindex 
  WHERE Doc_id = 1;
  
  

• Index Organized Table Enhancements
  There were a number of enhancements to index organized tables as of Oracle9i, including a lifting of the restriction against the use of bitmap indexes as secondary indexes for an IOT and the ability to create, rebuild, or coalesce secondary indexes on an indexed organized table. Oracle Database 10g continued this trend by allowing replication and all types of partitioning for index organized tables.
• Creating a Bitmap Index on an Index-Organized Table
  Bitmap indexes can be created on index-organized tables (IOT) as secondary indexes on the IOT, but they must be created with a mapping table. See the following example of creating the IOT:
  

  CREATE TABLE employees_part
  (
  EMPLOYEE_ID NUMBER(6) NOT NULL
  ,FIRST_NAME VARCHAR2(20)
  ,LAST_NAME VARCHAR2(25) NOT NULL
  ,EMAIL VARCHAR2(25) NOT NULL
  ,PHONE_NUMBER VARCHAR2(20)
  ,HIRE_DATE DATE NOT NULL
  ,JOB_ID VARCHAR2(10) NOT NULL
  ,SALARY NUMBER(8,2)
  ,COMMISSION_PCT NUMBER(2,2)
  ,MANAGER_ID NUMBER(6)
  ,DEPARTMENT_ID NUMBER(4)
  ,CONSTRAINT employees_part_pk PRIMARY KEY (employee_id, hire_date)
  )
  ORGANIZATION INDEX
  MAPPING TABLE;
  

SQL> CREATE BITMAP INDEX employees_part_1i
2 ON employees_part (department_id)
3 NOLOGGING
3 LOCAL;
Index created.

ON employees_part (department_id)
*
ERROR at line 2:

ORA-28669: bitmap index is not supported on index-organized tables

ALTER TABLE IOT_SALES ENABLE ROW MOVEMENT;

CREATE INDEX PROD_ID_IDX 
ON IOT_SALES(PROD_ID);

SQL> alter table employees_part move mapping table;
Table altered.