Databases Reference
In-Depth Information
For obvious reasons, you can't use the same number of key columns as is within the primary key for
the key compression factor specified in the
COMPRESS
clause because that represents a unique value, and
therefore no key compression is possible. You will receive the following error if you attempt to create the
table with the same compression factor as the number of primary key columns:
CREATE TABLE employees_iot
*
ERROR at line 1:
ORA-25194: invalid COMPRESS prefix length value
The compression occurs when there are duplicates within the columns of the primary key. For
instance, if the employee with
EMPLOYEE_ID
100 worked several jobs over the years, they would have
several entries for the
EMPLOYEE_ID/JOB_ID
combination. For rows with duplicates of the
EMPLOYEE_ID
itself, all repeated values would be compressed. See Table 4-1 for a brief example of the results from key
compression.
Table 4-1. Example of Key Compression for Employee_ID 100
Employee_ID
Job_ID
Employee_ID Value Compressed?
100
AD_ASST
NO (first entry)
100
IT_PROG
YES
100
AD_VP
YES
100
...
YES for all subsequent entries
Building Secondary Indexes
The index-organized table can be viewed the same as a heap-organized table in that if other indexes are
needed to speed query performance, secondary indexes can be added to index-organized tables because
they can be added on heap-organized tables. See the following example to create a secondary index on
DEPARTMENT_ID
of the
EMPLOYEES_IOT
table:
SQL> CREATE INDEX employees_iot_1i
2 ON employees_iot (department_id);
You can also create secondary indexes on partitioned IOTs.
SQL> CREATE INDEX employees_iot_1i
2 on employees_iot (department_id)
3 LOCAL;
The key difference between secondary indexes on heap-organized tables and secondary indexes on
index-organized tables is that there is no physical
ROWID
for each row in an index-organized table
