why does this statement take so long to return?

From: gdas <gdas1NOgdSPAM_at_yahoo.com.invalid>
Date: 2000/06/22
Message-ID: <3a4724e4.eb7a97bd@usw-ex0108-063.remarq.com>#1/1

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

The question I am trying to answer in this query is Show me the detail information for the top something (org_name). So my outer query
gets all the details and my innner query/in-line view generates who the top org is.

If I run the inner query stand-alone:

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

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:

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 = 2 group by al1.type, al4.org_name

it returns the results in about 1 second.

I realize that I'm doing some complicated sql here, and am willing to deal with a slower response time...but not this slow. I can't understand why this
takes so long. Logically, I would think that the total time for this query would be approximately 4 seconds. Since that is the sum of the times each part of the
query takes to run. Obviously in this case the time is not equal to the sum of the component parts.

All my statistics are current on all tables and indexes involved. I've got bitmap indexes on the low cardinality 'type' columns.

Does anyone have any ideas I can use to speed this up? (Besides creating a materialized view...I really don't want to have to maintain that).
Is there some kind of hint I can give it? Something else? How can I tell the optimizer that it has generated a bad plan?

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.

I'm really not sure where to start here as I'm not a tuning expert. In the past, when I saw a bad plan, it usually gave me a hint to recompute stats. Once I did that, the problems were usually resolved. However this time, I have recomputed the stats and I still have a bad plan and poor performance.

any help is appreciated,
Thanks,
Gavin

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jun 22 2000 - 00:00:00 CDT