Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: why does this statement take so long to return?

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

From: Dave Wotton <Dave.Wotton_at_dwotton.nospam.clara.co.uk>
Date: 2000/06/23
Message-ID: <uED45.353$Di7.50271@nnrp4.clara.net>#1/1

gdas wrote in message <3a4724e4.eb7a97bd_at_usw-ex0108-063.remarq.com>...
>I'm having a problem getting the following query to return in
>any kind of reasonable timeframe. Thus far, I have not seen it
>run to completion
>because I loose patience after waiting for 5 minutes at which
>point I kill it.
>
>
>select distinct al4.org_name, al1.type, count (al2.hit_id) from
>admin.datastage al1,
>admin.hit al2, admin.subject al3, admin.org al4
>where al1.unique_id=al2.unique_id and
>al2.subject_id=al3.subject_id and al3.org_id
>=al4.org_id and
>al4.org_id =
> (select org_id from
> (
> select aa.org_id, count(bb.hit_id) b from
> hit bb, org aa, datastage cc, subject dd
> where (bb.unique_id=cc.unique_id and
> bb.subject_id=dd.subject_id and
> dd.org_id=aa.org_id) and
> (aa.type='Regional' and
> cc.type in ('A','B','C','D') and
> cc.account_id=1) group by aa.org_id
> order by 2 desc
> )
> where rownum=1
> )
>group by al1.type, al4.org_name
>

 [snip]
>
>If I run the inner query stand-alone:
>

 [snip]
>
>it returns org_id = 2 and it does this in about 3 seconds.
>
>If I then run the outer query with constraint on org_id = 2 as
>follows:
>

 [snip]
>
>it returns the results in about 1 second.
>
>All my statistics are current on all tables and indexes involved.
>I've got bitmap indexes on the low cardinality 'type' columns.
>

 [snip]
>
>I did an explain plan on the statement and found the results
>quite shocking. Most of my indexes are not even being used.
>These are the indexes on the key columns being used in the joins
>in both the inner and outer queries.

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.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.
Received on Fri Jun 23 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US