RelationalDBDesign RelationalDBDesign 


Performance Tuning  «Prev  Next»
Lesson 5 Creating a secondary index
Objective Create a secondary index on index-organized table.

Creating secondary Indexes on Index Organized Tables

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.

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 ToolTip:
  1. Required keywords
  2. The name of the index you will create
  3. A list of columns in the index, separated by columns
CREATE INDEX index_name ON column_list;

Creating Secondary Index on Index Organized Table

Secondary index Functionality

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.

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;

Since the mapping table has been specified on the IOT, bitmap indexes can be created on the IOT.
SQL> CREATE BITMAP INDEX employees_part_1i
2 ON employees_part (department_id)
3 NOLOGGING
3 LOCAL;
Index created.
If no mapping table is specified on the IOT, you will receive the following error when attempting to create the bitmap index:
ON employees_part (department_id)
*
ERROR at line 2:
ORA-28669: bitmap index can not be created on an IOT with no mapping table
If you are attempting to create a bitmap index on an existing IOT with no mapping, simply alter the table. For example,
SQL> alter table employees_part move mapping table;
Table altered.

Creating Secondary Index on IOT - Exercise

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.