Range Scan Cost Fluctuations

I am running a large query that joins two tables, document(3 mil) and entity(9 mil). I have reorganized my tablespaces so that the two tables are on different tablespaces, different disks. They both have their indexes stored on a third tablespace. Before reconfiguring the production database, I was getting a range scan on index ix_document_8 that had a cost of 25. Now that I have reconfigured the prod database, the cost of the range scan has gone through the roof, 5933.

I have tried moving this one particular index to different tablespaces on all three disks available to me to no avail. I run "analyze index compute statistics;" on the index after every time I recreate it. It appears that no matter where I create this index it still has trouble accessing it. I have computed the statistics for the table before computing the index statistics. It appears to me that there is an I/O problem here. Why did it cost so little before and now it costs so much to access it?

Should the index be on the same disk/different tablespace? Different disk/different tablespace?

I have run out of my testing capabilities here to try and troubleshoot why the cost is so high. Could this be a problem with my CBO?

Any help is greatly appreciated,
-Dan Received on Sun Jun 13 2004 - 01:59:32 CEST