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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

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)

        (Cost=2 Card=1 Bytes=21)

 

 

 


The above text is an excerpt from:

Oracle SQL Tuning & CBO Internals
ISBN 0-9745993-3-6

by Kimberly Floss


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.