Oracle SQL Tuning and CBO Internal Indexed Organized Tables

Call (800) 766-1884 for Oracle support & training

Oracle Tips by Burleson

Oracle SQL Tuning and CBO Internal

Indexed Organized Tables

Another feature requiring the cost-based optimizer is indexed organized tables (IOTs). If a table is accessed using all primary key columns and the key columns are short, you might consider creating an indexed organized table. You can also specify an overflow tablespace for rows that are long. The rows are stored in a structure resembling a btree index. However, there are not two structures; there is not a data structure and a separate index structure. Instead, there is only one structure. The following code creates an IOT named stats_iot.

SQL> CREATE TABLE STATS_IOT

2 ( SNO PRIMARY KEY,

3 SNAME,

4 SEX,

5 REGION )

6 ORGANIZATION INDEX

7*AS SELECT * FROM STATS;

Table created.

The data dictionary view user_tablesshows an iot_type of IOT.

SQL> SELECT TABLE_NAME,

2 IOT_NAME,

3 IOT_TYPE

4 FROM USER_TABLES

5* WHERE TABLE_NAME = 'STATS_IOT';

TABLE_NAME IOT_NAME IOT_TYPE

---------- ------------------ --------

STATS_IOT IOT

The CREATE TABLE statement above causes Oracle to create a primary key on the SNO column of the IOT. The following query shows the information stored in user_indexes. Notice that the index SYS_IOT_TOP_24877 is created because of the PRIMARY KEY code in the CREATE TABLE statement.

SQL> SELECT INDEX_NAME,

2 INDEX_TYPE

3 FROM USER_INDEXES

4* WHERE TABLE_NAME = 'STATS_IOT';

INDEX_NAME INDEX_TYPE

----------------- ----------

SYS_IOT_TOP_24877 IOT – TOP

Oracle places the object number of the IOT in the name of the primary key index. All IOTs must have primary keys. The object number of the IOT STATS_IOT is 24877.

SQL> SELECT OBJECT_TYPE,

2 OBJECT_NAME,

3 STATUS

4 FROM USER_OBJECTS

5* WHERE OBJECT_ID = 24877;

OBJECT_TYPE OBJECT_NAME STATUS

----------- ----------- ------

TABLE STATS_IOT VALID

The following query uses the cost-based optimizer and the primary key index to retrieve one row although the IOT has not been analyzed.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT *

2 FROM STATS_IOT

3 WHERE SNO = 222;

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

(Cost=1 Card=1 Bytes=33)

1 0 INDEX (UNIQUE SCAN) OF

'SYS_IOT_TOP_24877' (UNIQUE)

(Cost=1 Card=1 Bytes=33)

After the IOT is analyzed, the EXPLAIN PLAN output in AUTOTRACE shows a cost of 2 instead of 1. Regardless, Oracle uses the cost-based optimizer to retrieve rows from an IOT.

SQL> ANALYZE TABLE STATS_IOT COMPUTE

STATISTICS;

Table analyzed.

SQL> SELECT *

2 FROM STATS_IOT

3* WHERE SNO = 222;

Execution Plan

-----------------------------------------

0 SELECT STATEMENT Optimizer=RULE

(Cost=2 Card=1 Bytes=21)

1 0 INDEX (UNIQUE SCAN) OF

'SYS_IOT_TOP_24877' (UNIQUE)