Re: why does this statement take so long to return?

In article <uED45.353$Di7.50271_at_nnrp4.clara.net>,   "Dave Wotton" <Dave.Wotton_at_dwotton.nospam.clara.co.uk> wrote:
>
> Without seeing the explain plan (please post it, or email it, along
> with a list of all indexes which reference columns used in the
> query), I can only guess how this is being executed. But my guess
> is that the inner query (3 secs) is being executed once for every
> org_id returned by the join in the calling query.
>
> You'll probably have to turn your query inside-out. ie. have the
> inner query as the main query, and the calling query as an embedded
> select ( select ... from ( select ... from ) ). To achieve what you
> want.
>
> Alternatively, is it possible to run your query as two separate
> queries, first run the inner query standalone and put the found
> org_id in a variable then pass this variable into the second query?
>
> It amazes me how often people create really complicated queries
> which could be broken down into a number of smaller queries called
> in sequence by the calling application. Of course, this depends
> on what you are calling the query from. There are occasions where
> a single (complicated) query is the only solution.
>
> Feel free to email me if you want more detailed assistance.
>
> Dave.

This is a good post, Dave. I enjoy your analysis. Last night (1 am) I spent at least 5 minutes reading the original message and couldn't figure out. One trivial caveat though. If you break the query into two, there's a finite probability that the second query is not reading the same picture as when you did it in one, atomic query.

Another possible rewrite is, instead of having that bulk subquery, combine the top-level select with the inner query into one level select. You know what I mean. Instead of:

select ename from emp where deptno = (select depno from dept where dname = 'accounting')

rewrite to:

select ename from emp a, dept b where a.deptno = b.deptno and b.dname = 'accounting';

so that the optimizer may do a better job.

--
Yong Huang

(yong321_at_yahoo.com)
(http://www.stormloader.com/yonghuang/)


Sent via Deja.com http://www.deja.com/
Before you buy.