Index-organized tables are ideal for situations in which a limited amount of information is associated with an index value. You can access the values in an index-organized table through the primary key or any valid prefix of the primary key.
There may be times when you want to add another index that is not based on the primary key to an index-organized table. Oracle gives you the ability to add these secondary indexes to index-organized tables.

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 ToolTip:

Secondary indexes on an index-organized table are createdin the same manner as regular indexes on regular tables, but function somewhat differently.
Remember how a normal index works: The index structure contains the index value, which then points to the associated row in the database table. The index entry points to the associated table through the use of a ROWID.
An index-organized table does not have any associated table rows, and therefore does not have any ROWIDs. Because of this, a secondary index on an index-organized table uses a logical ROWID. A logical ROWID is a ROWID that is based on a physical guess as to which block in the leaf node (of the index) contains a particular value. If the primary key of the value does not change, the logical ROWID remains accurate.
If the primary key value ever changes, the logical ROWID becomes invalid. The logical ROWIDs for an index-organized table are reassigned whenever the index is rebuilt.

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.

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:

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

Click the link below to practice creating a secondary index on an index-organized table.
Creating Secondary Index on IOT - Exercise
In the next lesson, you will learn about the limitations on index-organized tables.