By
Published: 04 Aug 2009
What is the difference between traditional tables and index organization tables? What do you advise me to use?
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.
Dig Deeper on Oracle database design and architecture
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.
Continue Reading