Re: Strange Cost Based Optimizer Decision Making

"Jimbo1" <nightfanguk_at_yahoo.co.uk> wrote in message
news:1135008948.124164.115450_at_g14g2000cwa.googlegroups.com...
> 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>

Of course, higher versions than 8i have the "index skip scan" feature :-p

PS that wasn't criticism, we are still running on 8i too :-)
Received on Mon Dec 19 2005 - 11:09:18 CST