Query tuning

From: Bruno D. <bd_avirer__at_arte-tv.com>
Date: Wed, 23 Oct 2002 09:49:22 +0200
Message-ID: <3db6546e$0$213$4d4eb98e@read.news.fr.uu.net>

Hi,

I don't care about tuning queries so far, but now i have the following problem:
(on oracle 9i release 2)

select t1.c1, t2.c2
from t1, t2
where c1=c2
and c2 in (select c3 from t3 where c4 like '%AAA%')

Tips: c1 is indexed, c2 is a primary key.

The sub query brings 14 lines only. In this case, Oracle will NOT use the index on t2.
It will last about 30 seconds.

If I replace the subquery by the 14 values, it works well --> near instant.

Of course, if I tell Oracle to use the Index with /*+ index (t2 idx) */ it works
but this query is a 'summary' of big query in which the /* index */ clause seems not to work.

Has anybody an idea ? Received on Wed Oct 23 2002 - 02:49:22 CDT