Solved

What is an index table?

Posted on 2001-07-03
5
887 Views
Last Modified: 2008-03-10

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) ?


Thanks,
0
Comment
Question by:chrbjo
5 Comments
 
LVL 2

Accepted Solution

by:
jammalk earned 25 total points
ID: 6248844
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?
Yes.

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))
    ORGANIZATION INDEX TABLESPACE ind_tbs
    PCTTHRESHOLD 20
    OVERFLOW TABLESPACE ovf_tbs;
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:
http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76956/tables.htm#3835
Cheers

0
 
LVL 2

Expert Comment

by:renuraj
ID: 6248853
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>(
        <col1>,
        <col2>,
         .,
         .,
         .,
        CONSTRAINT <primary key constraint name> PRIMARY KEY (<primary key columns>))
    ORGANIZATION INDEX

Advantages:
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.

Disadvantages:

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.


Regards,

0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7038055
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.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** 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.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7052605
Recommended disposition:
    Accept jammalk's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7052801
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now