Heap-organized table & Index-organized table
Figure 3-3 illustrates the structure of an index-organized
A heap-organized table is a table with rows stored in no particular order. This is a standard Oracle table; the term "heap" is used to differentiate it from an index-organized table or external table.
If a row is moved within a heap-organized table, the row's ROWID will also change.
Examples
CREATE TABLE t1 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30));
CREATE TABLE t1 (c1 NUMBER PRIMARY KEY, c2 VARCHAR2(30)) ORGANIZATION HEAP;
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 its definition, IOT indexes store all the columns of the table (see below for an exception to this rule - called the overflow area).
Properties and restrictions[edit]
- 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[edit]
- 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[edit]
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. The overflow segments can reside in a tablespace different from the main segments.
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.
The columns of the table that are recorded in the overflow segment are defined using the PCTHRESHOLD and/or INCLUDING options of the OVERFLOW clause (see example below).
Example of an IOT without an overflow area[edit]
The following example creates a simple IOT table and shows the objects and segments that are created. (This example was tested on Oracle versions 9.2 to 11.2.)
SQL> CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50))
2 ORGANIZATION INDEX;
Table created.
SQL> SELECT table_name, iot_type, iot_name FROM user_tables;
TABLE_NAME IOT_TYPE IOT_NAME
------------------------------ ------------ ------------------------------
MY_IOT IOT
SQL> SELECT index_name, index_type, table_name FROM user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IOT_TOP_71133 IOT - TOP MY_IOT
SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -------------------
71133 MY_IOT TABLE
71134 SYS_IOT_TOP_71133 INDEX
SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1;
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
SYS_IOT_TOP_71133 INDEX
- Note: In 11g, you must use the following syntax to see the same output:
CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50)) SEGMENT CREATION IMMEDIATE ORGANIZATION INDEX;
- The reason for is that by default in 11g the segment is created only when the first row is inserted.
As you can see 2 objects are created: the table and the index, but there is only 1 segment (implementation of the object) which is the index one.
The name of the index is by default "SYS_IOT_TOP_
" and its type is "IOT - TOP". You can choose the name of your index using the following syntax:
" and its type is "IOT - TOP". You can choose the name of your index using the following syntax:
SQL> CREATE TABLE my_iot (id INTEGER CONSTRAINT my_iot_pk PRIMARY KEY, value VARCHAR2(50))
2 ORGANIZATION INDEX;
Table created.
SQL> SELECT table_name, iot_type, iot_name FROM user_tables;
TABLE_NAME IOT_TYPE IOT_NAME
------------------------------ ------------ ------------------------------
MY_IOT IOT
SQL> SELECT index_name, index_type, table_name FROM user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
MY_IOT_PK IOT - TOP MY_IOT
SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -------------------
71135 MY_IOT TABLE
71136 MY_IOT_PK INDEX
SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1;
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
MY_IOT_PK INDEX
The IOT_NAME column is empty and will be useful in the example of the next section.
Example of an IOT with an overflow area[edit]
The following example creates an IOT with an overflow area and shows the objects and segments that are created. (This example was tested in versions 9.2 to 11.2, see the note in the previous section about 11g.)
SQL> CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50), comments varchar2(1000))
2 ORGANIZATION INDEX
3 INCLUDING value OVERFLOW;
Table created.
SQL> SELECT table_name, iot_type, iot_name FROM user_tables ORDER BY 1;
TABLE_NAME IOT_TYPE IOT_NAME
------------------------------ ------------ ------------------------------
MY_IOT IOT
SYS_IOT_OVER_71142 IOT_OVERFLOW MY_IOT
SQL> 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_IOT COMMENTS
SQL> SELECT index_name, index_type, table_name, include_column FROM user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME INCLUDE_COLUMN
------------------------------ --------------------------- ------------------------- --------------
SYS_IOT_TOP_71142 IOT - TOP MY_IOT 2
SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------- -------------------
71142 MY_IOT TABLE
71143 SYS_IOT_OVER_71142 TABLE
71144 SYS_IOT_TOP_71142 INDEX
SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1;
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
SYS_IOT_OVER_71142 TABLE
SYS_IOT_TOP_71142 INDEX
All the columns up to and including the one named in the INCLUDING option of the OVERFLOW clause are in the top segment; the remaining ones are in the overflow segment.
Here, we see that 2 table objects are created, the main one is of iot_type IOT and the overflow one is of iot_type IOT_OVERFLOW. The name of the overflow table is SYS_IOT_OVER_
. You can see now the purpose of the IOT_NAME column, it gives the name of the IOT table for its overflow one.
. You can see now the purpose of the IOT_NAME column, it gives the name of the IOT table for its overflow one.
You can also see that 2 segments are created: the index of the IOT and the overflow area.
In the end, the USER_INDEXES view gives you the last column included in the index in its INCLUDE_COLUMN column.
FAQ[edit]
How to move an IOT into another tablespace?[edit]
Based on the example used in the previous section:
SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ SYS_IOT_OVER_71142 TABLE TS_D01 SYS_IOT_TOP_71142 INDEX TS_D01
Let's assume you want to move all the segments from their current tablespace to another tablespace. You'll quickly discover that you cannot use the standard commands:
SQL> ALTER TABLE sys_iot_over_71142 MOVE TABLESPACE ts_i01;
ALTER TABLE sys_iot_over_71142 MOVE TABLESPACE ts_i01
*
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table
SQL> ALTER INDEX sys_iot_top_71142 REBUILD TABLESPACE ts_i01;
ALTER INDEX sys_iot_top_71142 REBUILD TABLESPACE ts_i01
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
You have to first find the name of the associated IOT table:
SQL> SELECT iot_name FROM user_tables WHERE table_name = 'SYS_IOT_OVER_71142'; IOT_NAME ------------------------------ MY_IOT SQL> SELECT table_name FROM user_indexes WHERE index_name = 'SYS_IOT_TOP_71142'; TABLE_NAME ------------------------------ MY_IOT
Then you can move the segments:
SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_i01; Table altered.
However, this is only the main part of the IOT - the overflow part continues to reside in its original tablespace:
SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ SYS_IOT_OVER_71142 TABLE TS_D01 SYS_IOT_TOP_71142 INDEX TS_I01
To move the overflow area also, an additional statement is necessary:
SQL> ALTER TABLE my_iot MOVE OVERFLOW TABLESPACE ts_i01; Table altered. SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ SYS_IOT_OVER_71142 TABLE TS_I01 SYS_IOT_TOP_71142 INDEX TS_I01
Both steps can be combined in one statement:
SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_d01 OVERFLOW TABLESPACE ts_d01; Table altered.
How to know which columns are in the overflow segment?[edit]
Remember that the INCLUDE_COLUMN of the USER_INDEXES view gives you the last column included in the index and so in the top segment, so joining this view with the USER_TAB_COLUMNS you can separate the columns that are in the top segment from those that are in the overflow one as with the following statement:
SQL> SELECT c.table_name, c.column_name, 2 CASE WHEN c.column_id <= i.include_column THEN 'TOP' ELSE 'OVERFLOW' END segment 3 FROM user_tab_columns c, user_indexes i 4 WHERE i.table_name = c.table_name 5 ORDER by table_name, column_id; TABLE_NAME COLUMN_NAME SEGMENT ------------------------------ ------------------------------ -------- MY_IOT ID TOP MY_IOT VALUE TOP MY_IOT COMMENTS OVERFLOW
Index-Organized Table Characteristics
The database system performs all operations on index-organized tables by manipulating the B-tree index structure. Table 3-4 summarizes the differences between index-organized tables and heap-organized tables.
| Heap-Organized Table | Index-Organized Table |
|---|---|
The rowid uniquely identifies a row. Primary key constraint may optionally be defined.
|
Primary key uniquely identifies a row. Primary key constraint must be defined.
|
Logical rowid in
ROWID pseudocolumn allows building secondary indexes. | |
Individual rows may be accessed directly by rowid.
|
Access to individual rows may be achieved indirectly by primary key.
|
Sequential full table scan returns all rows in some order.
|
A full index scan or fast full index scan returns all rows in some order.
|
Can be stored in a table cluster with other tables.
|
Cannot be stored in a table cluster.
|
Can contain a column of the
LONG data type and columns of LOB data types. |
Can contain LOB columns but not
LONG columns. |
Can contain virtual columns (only relational heap tables are supported).
|
Cannot contain virtual columns.
|
B-Tree Indexes
B-trees, short for balanced trees, are the most common type of database index. A B-tree index is an ordered list of values divided into ranges. By associating a key with a row or range of rows, B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
Figure 3-1 illustrates the structure of a B-tree index. The example shows an index on the
department_id column, which is a foreign key column in theemployees table.
Branch Blocks and Leaf Blocks
A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. In Figure 3-1, the root branch block has an entry
0-40, which points to the leftmost block in the next branch level. This branch block contains entries such as 0-10 and 11-19. Each of these entries points to a leaf block that contains key values that fall in the range.
A B-tree index is balanced because all leaf blocks automatically stay at the same depth. Thus, retrieval of any record from anywhere in the index takes approximately the same amount of time. The height of the index is the number of blocks required to go from the root block to a leaf block. The branch level is the height minus 1. In Figure 3-1, the index has a height of 3 and a branch level of 2.
Branch blocks store the minimum key prefix needed to make a branching decision between two keys. This technique enables the database to fit as much data as possible on each branch block. The branch blocks contain a pointer to the child block containing the key. The number of keys and pointers is limited by the block size.
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. Each entry is sorted by (key, rowid). Within a leaf block, a key and rowid is linked to its left and right sibling entries. The leaf blocks themselves are also doubly linked. In Figure 3-1 the leftmost leaf block (
0-10) is linked to the second leaf block (11-19).
====*************=========
departments table. The leaf blocks contain the rows of the table, ordered sequentially by primary key. For example, the first value in the first leaf block shows a department ID of 20, department name of Marketing, manager ID of 201, and location ID of 1800.
An index-organized table stores all data in the same structure and does not need to store the rowid. As shown in Figure 3-3, leaf block 1 in an index-organized table might contain entries as follows, ordered by primary key:
20,Marketing,201,1800 30,Purchasing,114,1700
Leaf block 2 in an index-organized table might contain entries as follows:
50,Shipping,121,1500 60,IT,103,1400
A scan of the index-organized table rows in primary key order reads the blocks in the following sequence:
- Block 1
- Block 2
To contrast data access in a heap-organized table to an index-organized table, suppose block 1 of a heap-organized
departments table segment contains rows as follows:50,Shipping,121,1500 20,Marketing,201,1800
Block 2 contains rows for the same table as follows:
30,Purchasing,114,1700 60,IT,103,1400
A B-tree index leaf block for this heap-organized table contains the following entries, where the first value is the primary key and the second is the rowid:
20,AAAPeXAAFAAAAAyAAD 30,AAAPeXAAFAAAAAyAAA 50,AAAPeXAAFAAAAAyAAC 60,AAAPeXAAFAAAAAyAAB
A scan of the table rows in primary key order reads the table segment blocks in the following sequence:
- Block 1
- Block 2
- Block 1
- Block 2
Thus, the number of block I/Os in this example is double the number in the index-organized example.
Comments
Post a Comment
Oracle DBA Information