Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 1 May 2012

How to Manage Index-organized table in Oracle 9i

An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure while Normal ‎relational tables, called heap-organized tables, store rows in any order (unsorted). In contrast to this, index-‎organized tables store rows in a B-tree index structure that is logically sorted in primary key order. Unlike ‎normal primary key indexes, which store only the columns included in it definition, IOT indexes store all the ‎columns of the table.‎

Properties and restrictions                                 

§         An IOT must contain a primary key.
§         Rows are accessed via a logical rowid and not a physical rowid like in heap-organized tables.
§         An IOT cannot be in a cluster.
§         An IOT cannot contain a column of LONG data type.
§         You cannot modify an IOT index property using ALTER INDEX (error ORA-25176), you must use an ALTER TABLE instead.

Advantages of an IOT

§         As an IOT has the structure of an index and stores all the columns of the row, accesses via primary key conditions are faster as they don't need to access the table to get additional column values.
§         As an IOT has the structure of an index and is thus sorted in the order of the primary key, accesses of a range of primary key values are also faster.
§         As the index and the table are in the same segment, less storage space is needed.
§         In addition, as rows are stored in the primary key order, you can further reduce space with key compression.
§         As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized.

Row overflow area

If some columns of the table are infrequently accessed, it is possible to offload them into another segment named the overflow area. An overflow segment will decrease the size of the main (or top) segment and will increase the performance of statements that do not need access the columns in the overflow area.
Notes:
§         The overflow area can contains only columns that are not part of the primary key.
§         If a row cannot fit in a block, you must define an overflow area.
§         Consequently, the primary key values of an IOT must fit in a single block.

Example of an IOT without an overflow area (Output is Tested or Pasted from Toad)

CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2 (50))
ORGANIZATION INDEX;

SELECT table_name, iot_type, iot_name FROM user_tables;
TABLE_NAME       IOT_TYPE           IOT_NAME
----------      ------------       ------------
MY_IOT           IOT

SELECT index_name, index_type, table_name FROM user_indexes;     
INDEX_NAME        INDEX_TYPE          TABLE_NAME
-------------     -----------         -----------
SYS_IOT_TOP_36163 IOT - TOP           MY_IOT

SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;
OBJECT_ID         OBJECT_NAME             OBJECT_TYPE
----------        --------------------    -------------------
36163             MY_IOT                  TABLE
36164             SYS_IOT_TOP_36163       INDEX

SELECT segment_name, segment_type FROM user_segments ORDER BY 1;
SEGMENT_NAME            SEGMENT_TYPE
--------------------    ------------------
SYS_IOT_TOP_36163       INDEX
The name of the index is by default "SYS_IOT_TOP_<table object number>" and its type is "IOT - TOP". You can choose the custom index name.

IOT with an overflow area (Output is Tested and Pasted from Toad)

CREATE TABLE my_iot1 (id INTEGER PRIMARY KEY, value VARCHAR2(50), comments varchar2 (1000)) ORGANIZATION INDEX INCLUDING value OVERFLOW;

SELECT table_name, iot_type, iot_name FROM user_tables ORDER BY 1;
TABLE_NAME              IOT_TYPE          IOT_NAME
----------------       ------------      -----------------------
MY_IOT                  IOT
MY_IOT1                 IOT
SYS_IOT_OVER_36165      IOT_OVERFLOW      MY_IOT1

SELECT table_name, column_name FROM user_tab_columns ORDER by table_name, column_id;
TABLE_NAME             COLUMN_NAME
----------------       ------------------------------
MY_IOT                 ID
MY_IOT                 VALUE
MY_IOT1                ID
MY_IOT1                VALUE
MY_IOT1                COMMENTS                                           

SELECT index_name, index_type, table_name, include_column FROM user_indexes;
INDEX_NAME        INDEX_TYPE         TABLE_NAME      INCLUDE_COLUMN
---------------   -----------        -----------     -----------------
SYS_IOT_TOP_36163 IOT - TOP          MY_IOT          0
SYS_IOT_TOP_36165 IOT - TOP          MY_IOT1         2

SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;
OBJECT_ID   OBJECT_NAME             OBJECT_TYPE
 ---------- --------------------    -------------------
  36163     MY_IOT                  TABLE
  36164     SYS_IOT_TOP_36163       INDEX
  36165     MY_IOT1                 TABLE
  36166     SYS_IOT_OVER_36165      TABLE
  36166     SYS_IOT_OVER_36165      TABLE

SELECT segment_name, segment_type FROM user_segments ORDER BY 1;
SEGMENT_NAME                  SEGMENT_TYPE
--------------------          ------------------
SYS_IOT_OVER_36165            TABLE
SYS_IOT_OVER_36165            INDEX
SYS_IOT_TOP_36165             INDEX

How to move an IOT into another tablespace?

SELECT iot_name FROM user_tables WHERE table_name = 'SYS_IOT_OVER_36165';
IOT_NAME
------------------------------
MY_IOT1

SELECT table_name FROM user_indexes WHERE index_name = 'SYS_IOT_TOP_36165';
TABLE_NAME
------------------------------
MY_IOT1
ALTER TABLE my_iot MOVE TABLESPACE users;
ALTER TABLE my_iot1 MOVE OVERFLOW TABLESPACE users;

SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1;
SEGMENT_NAME           SEGMENT_TYPE            TABLESPACE_NAME
------------            ------------            ----------------
SYS_IOT_OVER_36165      TABLE                   USERS
SYS_IOT_TOP_36163       INDEX                   USERS
SYS_IOT_TOP_36165       INDEX                   USERS

The first statement moves the top segment and the second one the overflow segment. This can be done also by using single statement.
ALTER TABLE my_iot MOVE TABLESPACE users OVERFLOW TABLESPACE users;

How to know which columns are in the overflow segment?

SELECT c.table_name, c.column_name, CASE WHEN c.column_id <= i.include_column THEN 'TOP' ELSE 'OVERFLOW' END segment  FROM user_tab_columns c, user_indexes i WHERE i.table_name = c.table_name ORDER by table_name, column_id;
TABLE_NAME              COLUMN_NAME             SEGMENT
---------------         ----------------        --------
MY_IOT                  ID                      OVERFLOW
MY_IOT                  VALUE                   OVERFLOW
MY_IOT1                 ID                      TOP
MY_IOT1                 VALUE                   TOP
MY_IOT1                 COMMENTS                OVERFLOW

0 comments:

Post a Comment