Ask the Expert

Traditional table vs. Index Organized Table (IOT) in Oracle

What is the difference between traditional tables and index organization tables? What do you advise me to use?

    Requires Free Membership to View

A traditional table stores the data in a "heap" structure, or a big pile of data. The Index Organized Table (IOT) stores the data in a B-tree index data structure. In a heap table, the data has no particular order. In an IOT, the data is ordered by the table's primary key.

It is best to use an IOT when you only query the table's contents by specifying the primary key in the WHERE clause. If you query other, non-PK columns in the WHERE clause, you may find the IOT will slow down your performance.

The only way to know for sure if the IOT will work in your Oracle environment is to test it out with your application.

This was first published in August 2009

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: