Re: Strange Cost Based Optimizer Decision Making

Hello all,

Looks like I'll have to eat the words of my previous posting. This could well be the problem. The order of columns in the composite indexes are different. TIME_STAMP is the first column in the index on HUGE_TABLE. It's the last column in the index on LARGE_TABLE.

Here's the proof:

SQL> SELECT * FROM user_ind_columns
  2 WHERE index_name IN ('LARGE_TABLE_PK', 'HUGE_TABLE_PK')   3 ORDER BY index_name, column_position;

INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH DESC

--------------------- -------------------- ---------------
--------------- ------------- ----
HUGE_TABLE_PK         HUGE_TABLE           TIME_STAMP
 1             7 ASC
HUGE_TABLE_PK         HUGE_TABLE           LINK_ID
 2            22 ASC
LARGE_TABLE_PK        LARGE_TABLE          SECTION_ID
 1            22 ASC
LARGE_TABLE_PK        LARGE_TABLE          TIME_STAMP
 2             7 ASC

Thanks very much for pointing that out to me Spendius.

Much appreciated. I'll recreate the index and re-rerun this test. That's going to take a while, but I'll update this post with the outcome later for anyone who might be interested.

Cheers.

James Received on Mon Dec 19 2005 - 10:15:48 CST