What is an index table?

What is an index table? Does it mean that you store the whole table as an index? What are the dis- and advantages with using index tables?

Where can I find examples on how this is done (e.g. scripts) ?

Who is Participating?
jammalkConnect With a Mentor Commented:
1. What is an index table (IOT-Index Organized Table)?
Index-organized tables are tables with data rows grouped according to the primary key. This clustering is achieved using a B*-tree index. B*-tree indexes are special types of index trees that differ from regular table B-tree indexes in that they store both the primary key and non-key columns. The attributes of index-organized tables are stored entirely within the physical data structures for the index.

2. Does it mean that you store the whole table as an index?

3. What are the dis- and advantages with using index tables?
Index-organized tables are like regular tables with a primary key index on one or more of its columns. However, instead of maintaining two separate storage spaces for the table and B*tree index, an index-organized table only maintains a single B*tree index containing the primary key of the table and other column values.
Index-organized tables are suitable for accessing data by way of primary key or any key that is a valid prefix of the primary key. There is no duplication of key values and storage requirements are reduced because a separate index structure containing the key values and ROWID is not created.
Index-organized tables are particularly useful when you are using applications that must retrieve data based on a primary key. Index-organized tables are also suitable for modeling application-specific index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables.
Index-organized tables provide fast key-based access to table data for queries involving exact match and range searches. Changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure (because there is no separate table storage area).
Also, storage requirements are reduced because key columns are not duplicated in the table and index. The remaining non-key columns are stored in the index structure.

4. Examples
CREATE TABLE docindex(
        token char(20),
        doc_id NUMBER,
        token_frequency NUMBER,
        token_offsets VARCHAR2(512),
        CONSTRAINT pk_docindex PRIMARY KEY (token, doc_id))
You use the CREATE TABLE statement to create index-organized tables, but you will need to provide the following additional information:
** An ORGANIZATION INDEX qualifier, which indicates that this is an index-organized table.
** A primary key, specified through a column constraint clause (for a single column primary key) or a table constraint clause (for a multiple-column primary key). A primary key must be specified for index-organized tables.
** An optional row overflow specification clause (OVERFLOW), which preserves dense clustering of the B*tree index by storing the row column values exceeding a specified threshold in a separate overflow data segment. An INCLUDING clause can also be specified to specify what (non-key) columns are to be stored in the overflow data segment.
** A PCTTHRESHOLD value which defines the percentage of space reserved in the index block for an index-organized table. Any portion of the row that exceeds the specified threshold is stored in the overflow segment. In other words, the row is broken at a column boundary into two pieces, a head piece and tail piece. The head piece fits in the specified threshold and is stored along with the key in the index leaf block. The tail piece is stored in the overflow area as one or more row pieces. Thus, the index entry contains the key value, the non-key column values that fit the specified threshold, and a pointer to the rest of the row.

If you want more info, you can go to:

I think you are talking about index-organized tables.

Index-organized tables are like regular tables with a primary key index on one or more of its columns.

Yes, the rows are stored in the form of an index, but primary key is compulsory.

To create an index-organized table, just add ORGANIZATION INDEX at the end of normal CREATE TABLE statement

CREATE TABLE <table name>(
        CONSTRAINT <primary key constraint name> PRIMARY KEY (<primary key columns>))

1. Because data rows are stored in the index, index-organized tables provide faster key-based access to table data for queries that involve exact match or range search or both.
2. The storage requirements are reduced because key columns are not duplicated as they are in an ordinary table and its index. The data row stored with the key in an index-organized table only contains non-key column values.
3. Also, placing the data row with the key eliminates the additional storage that an index on an ordinary table requires for physical rowids, which link the key values to corresponding rows in the table.

This option is good for the tables like master tables with smaller no of frequent inserts and deletes.


It is not good for the tables with huge amount of data manipulation(i.e. inserts and deletes), by which the performance will come down.


Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.



** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
Recommended disposition:
    Accept jammalk's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
Force accepted

** Mindphaser - Community Support Moderator **
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.