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

From: Isaac Blank <izblank_at_yahoo.com>
Date: 2000/06/28
Message-ID: <39594912.5652798@news.nanospace.com>#1/1

try this:

select distinct al4.org_name, al1.type, count (al2.hit_id) from admin.datastage al1,
admin.hit al2, admin.subject al3, admin.org al4,

    (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
    ) kludge
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 = kludge.org_id
group by al1.type, al4.org_name

The idea is that the subquery in question will now have much more chances to be executed only once

On Thu, 22 Jun 2000 15:24:31 -0700, gdas <gdas1NOgdSPAM_at_yahoo.com.invalid> wrote:

>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 Wed Jun 28 2000 - 00:00:00 CDT