Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: IOT - comment about high writes

RE: IOT - comment about high writes

From: Reardon, Bruce (CALBBAY) <>
Date: Tue, 30 Oct 2001 18:38:47 -0800
Message-ID: <>

This message was sent to the list back in August and seemed to recommend that IOTs shouldn't be used for high write tables.

Would anyone like to help explain why this might be a bad idea?

Bruce Reardon

-----Original Message-----
Sent: Wednesday, 29 August 2001 7:18

If your grabing all rows, and your calling all columns (some in order, some display) why wouldn't it do a fts?
There is no reason to use the index.

Now if you overloaded the index, it may opt to use the index, but to what avail? It is is the same size of the table.  

In overloading, I refere to puting PersonalCode at the end of the index you created, this way all teh data will be available from the index, there will be no need to go to the table, altough the table will be the same size as the index, but the index will already be sorted.  

Perhaps you may want to look at IOT, if this isn't high write table.

Oracle DBA
Phone: (978) 322-5744
Fax: (707) 885-2275
73 Princeton Street
North, Chelmsford 01863   

-----Original Message-----
Sent: Tuesday, August 28, 2001 3:37 PM


I have a table with 3 colums : PersonalCode, Name and SpecialOrder.

The PersonalCode is the primary index and I have also an index on SpecialOrder,Name columns.

However the following select:

Select PersonalCode From MyTable
  Order By SpecialOrder,Name

doesn't use the SpecialOrder,Name index at all. If I watch it in Explain Plan window I can see that :

SELECT STATEMENT, GOAL = CHOOSE                 1181    16378   2456700
 SORT ORDER BY                  1181    16378   2456700
  TABLE ACCESS FULL     ADMIN   MyTable 69      16378   2456700

How can I make the index work?

Thank you in advance

Zsolt Csillag

Please see the official ORACLE-L FAQ:

Author: Reardon, Bruce (CALBBAY)

San Diego, California        -- Public Internet access / Mailing Lists

Original text of this message