Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: why does this statement take so long to return?
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