Re: Please help me solve this Oracle mystery...

Using the /*+ ORDERED */ hint and changing the order of the tables in the FROM clause will tell you if the PLAN is changing after this table is rebuilt. While in SQL*Plus, use "set autotrace on" to see how the query is accessed before and after. But, if you are constantly purging records from the table and the PCTUSED is not set high enough, than the blocks may not be being reused (adds more instead). PCTUSED is the percent that the block must fall below before it is used again (ie a PCTUSED of 40% must mean that the block must be 60% free before it is reused)...also, the indexes do not have a PCTUSED storage parameter and should be rebuilt if a lot of records are purged since they can get very large (and be very empty). If an index is getting larger than the table, then this is probably an issue and the index needs to be rebuilt. With 7.2+, you can use the "alter index ... rebuild" to create an index fast using the original index instead of going to the table.

Some ideas,

Rich Niemiec
Oracle Performance Tips and Techniques
Oracle Press
ISBN: 0078824346 Received on Mon Jun 07 1999 - 14:17:33 CDT