Re: SQL Optimization

Gilles WIART wrote:
>
> Hello,
>
> I have a simple question about SQL optimization. I would like to know why
> this SQL query
>
> select count(*) from notices
> where code in (
> select code from index_motscles where (idx like 'FRANCAIS')
> intersect
> select code from notices where contains(titre, 'eleves') > 0
> )
>
> is faster (really faster) than this one :
>
> select count(*) from notices
> where
> code in ( select code from index_motscles where (idx like
> 'FRANCAIS') )
> and
> contains(titre, 'eleves') > 0
>
> The result is the same, but the execution time of the first query is 1
> second, and the execution time of the second one is 20 seconds !!!
>
> Do I need to use "intersect" everytime to optimize my queries ?
>
> Thanks for your answers !
>
> GilloS

Try issuing:

set autotrace on
or
alter session set sql_trace = true

and then run the SQL's. The trace and plan information could be of assitance here.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."