Re: Tune SQL query

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Fri, 10 Dec 1999 22:34:14 +0100
Message-ID: <944861702.19784.0.pluto.d4ee154e@news.demon.nl>

We need more background. By this I mean

- primary keys, foreign keys
- indexes
- query plan
- which optimizer are you using

Usually a function on an indexed column is the best way to force a full table scan
Also my experience is subqueries of this type can be better formulated as where exists
correlated subqueries especially with rule-based optimizer.

Hth

--
Sybrand Bakker, Oracle DBA
<weijendavid_at_my-deja.com> wrote in message news:82rpoe$g3p$1_at_nnrp1.deja.com...
> Hi, all
>
> Does anybody know how to tune the query below so it doesn't take
> "Forever" to run, yes, I mean forever...since it never return.
> Here is the query:
>
> -----------------------------------------------------------------------
> delete from caritemoption o where o.caritemid in
> (select ci.caritemid from caritem ci, car c
> where ci.carid = c.carid
> and upper(c.first_name) = 'xxx'
> or upper(c.last_name) = 'yyyy');
> -----------------------------------------------------------------------
> There are so many methods introduced in the book but it just makes
> things more confusing. Please help.
>
> Thank you.
>
> David.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Dec 10 1999 - 15:34:14 CST