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