I presume that you are on 8.0, because 8.1 does better here. The problem is that Oracle is not able to compute the selectivity of the pair of predicates on P_DTM effectively. Under 8.0 histogram endpoints are represented as hashed numbers. This means that for character data, endpoints are not distinguished unless they are distinct within the first 5 characters. The maximum and minimum values that Oracle needs to use to compute the selectivity of these predicates are represented in the data dictionary as a single bucket histograms. Thus Oracle selects the fast full index scan and rejects the index range scan access path to this table because the histogram endpoints suggest that too much of the index would be read. If you can upgrade to 8.1 this problem will go away, because Oracle now also records the actual endpoint values in the data dictionary.
Have you come across any performance related problems related to IOTs? We have a table with about 40 million rows and about 4 GB in size. Earlier this table was a regular table (not IOT) and we had issues with the index size since we were archiving data from this table and index size was increasing. We could not rebuild the index owing to the size of the table (which needed huge temp segment size). Therefore we recreated this table as index organized and since then we are having performance problems. for example a query which used to take couple of seconds , now takes 15/20 minutes. My check of waits and events show up db file parallel read. since this tables is IOT , it has primary key and the query uses all the columns of primary key in the where clause.
Also to explain better with an example
Select count(p_mtrid) from UTSPROFILE where p_mtrid = 'SC-SONGS-2' and p_mtrchn = '4' and p_dtm >= '200007010100' and p_dtm <= '200008010100'This is the query which is having performance problems. This particular query used to work fine (performance wise) before the table as index organized. Just to contrast , some variation of the above query like mentioned below still give good response before and after index organized table was created.
Select count(p_mtrid) from UTSPROFILE where p_mtrid = 'SC-SONGS-2' and p_mtrchn = '4' and p_dtm like '20000701%' Select count(p_mtrid) from UTSPROFILE where p_mtrid = 'SC-SONGS-2' and p_mtrchn = '4' and p_dtm = '200007010000'Is there something that I have overlooked when creating index organized table ?