Re: Query tuning

Bruno D. wrote:

> 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.

This is because you are doing away with the sub-select - which shows that the majority of the processing time is spend on running that query on t3.

Also likely implies (looking at there where clause) that it is doing a full table scan of t3 (of which only 14 rows matches the criteria). This means that _all_ the rows in t3 is read to find those 14 matches.

The join between t1 and t2 is not the problem. The select on t3 is.

Kneejerk. Using a LIKE on a column often mean that data normalisation has been ignored. This is Very Bad (tm). I still find it horrendous working with people daily who design, develop and work with databases, who have _no_ idea about 3rd normal form. I hope this is not the case here.

--
Billy