Index-Organized Tables - Expert Indexing in Oracle Database 11g - page 72

greater than the percentage of the index block specified by the PCTTHRESHOLD parameter (the default is
50), every column that exceeds the threshold will be stored in the overflow area. Also, you can specify the
overflow segment to a specific tablespace, if desired.
■ Tip Use ANALYZE TABLE...LIST CHAINED ROWS command to determine if you have set PCTTHRESHOLD
appropriately.
In contrast to the PCTTHRESHOLD parameter, there is the INCLUDING clause, which specifies the last
table column for the row data that will be stored in the B-tree index segment. All columns after the
column specified by the INCLUDING clause will be stored in the overflow area. It is possible to specify both
the PCTTHRESHOLD and INCLUDING clauses, as shown in the following example:
SQL> CREATE TABLE employees
2 (
3 EMPLOYEE_ID NUMBER(6) NOT NULL
4 ,FIRST_NAME VARCHAR2(20)
5 ,LAST_NAME VARCHAR2(25) NOT NULL
6 ,EMAIL VARCHAR2(25) NOT NULL
7 ,PHONE_NUMBER VARCHAR2(20)
8 ,HIRE_DATE DATE NOT NULL
9 ,JOB_ID VARCHAR2(10) NOT NULL
10 ,SALARY NUMBER(8,2)
11 ,COMMISSION_PCT NUMBER(2,2)
12 ,MANAGER_ID NUMBER(6)
13 ,DEPARTMENT_ID NUMBER(4)
14 ,CONSTRAINT employees_pk PRIMARY KEY (employee_id)
15 )
16 ORGANIZATION INDEX
17 TABLESPACE empindex_s
18 PCTTHRESHOLD 40
19 INCLUDING salary
20 OVERFLOW TABLESPACE overflow_s
Table created.
See Figure 4-1 for an illustration of an index-organized EMPLOYEES table row as stored in the table, as
well as the overflow segment. In the example, you can see that the primary key in the EMPLOYEES table is
the EMPLOYEE_ID , and the root block, branch blocks, and leaf blocks are structured based on the primary
key. Within the leaf blocks themselves is the primary key, as well as all of the non-key columns up
through the SALARY column, which corresponds to the INCLUDING clause in the CREATE TABLE DDL
statement. All column data after the SALARY column is therefore stored in the overflow segment.
For performance reasons, the order of columns within an index-organized table is important, unlike
normal heap-organized tables. This is simply because of the overflow segment. The most queried
columns should not be placed in the overflow segment, simply because it is an extra I/O operation to
retrieve the remaining column data for a given row. For this reason, the least queried columns should be
placed on the trailing end of the table DDL, especially those after the column specified in the INCLUDING