Re: Query tuning

From: Michael Gast <mig-sm_at_web.de>
Date: Wed, 23 Oct 2002 10:18:50 +0200
Message-ID: <3DB65B6A.1030305@web.de>

Hi Bruno,

Bruno D. schrieb:
> 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%')

                                        ^^^^^^^^^^^^

The leading '%' sign in your search string causes a full table scan on table t3. An index on c4 cannot be used. If you have a look on the execution plan, i assume you will find the full table scan on t3 in the deepest nested loop.

Are you sure that your column c4 is normalized? Please check, if it is possible to built up at least two columns (one for the string represented by the '%', one for your search string and probably one for the trailing '%')?

-- 
All emails sent to this address are never read and never will be
answered. Sorry, but until someone cleans up the spam mess, that's the
way it has to be.

E-Mails, die direkt an diese Adresse geschickt werden, lese und
beantworte ich nicht. Ich bedauere diesen Umstand sehr, kenne derzeit
aber keine bessere Möglichkeit, um die Spam-Flut abzustellen.

Mit freundlichen Grüßen / Best Regards
Michael Gast
SEPP MED GmbH

Received on Wed Oct 23 2002 - 03:18:50 CDT