Re: Slow SQL, too many logical reads ?

"Peter" <peternybor_at_gmail.com> wrote in message news:1178554012.265020.88360_at_y80g2000hsf.googlegroups.com...
> On May 7, 5:21 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
>
> Thanks, I will probably try your index tip (reversing the third and
> fourth columns) one of these days (... but I'm not the only one using
> the database, and the table is rather big, 48M rows).
>
> Is it possible to force (hint or something) the inlist to be delayed
> to a filter predicate ? As you say, it might make things worse, but I
> would like to try anyway, as the first column, IA_IACT2, is very
> selective (20M distinct values), while the third (inlist) column,
> IA_SEQUE, is very un-selective (only 13 distinct values).
>
> Also, see my May 1, 7:15 pm posting, where I tried removing the
> predicate
> --AND IA.IA_SEQUE IN ( '17', '2', '4', '9', '11', '6', '1', '3', '10',
> '5', '0' )
> with big performance gains.
>
> Regards,
>
> -- Peter
>

Try changing the predicate to

    AND IA.IA_SEQUE||'' IN ( '17', '2', '4', '9', '11', '6', '1', '3','10', '5', '0' )

In your version of Oracle this should still be resolved in the index (earlier versions would postpone it to the table visit) but as a FILTER rather than an ACCESS predicate

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html