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

From: gdas <gdas1NOgdSPAM_at_yahoo.com.invalid>
Date: 2000/06/27
Message-ID: <2308e887.c41854a7@usw-ex0102-013.remarq.com>#1/1

Thanks for the response Dave,

first to answer some questions. All my key fields (subject_id, hit_id, unique_id etc...) are all numbers, defined as number with no scale or precision (if that makes any difference). hit.subject_id and subject.subject_id are both defined exactly the same (number).

I tried your hint as follows and did receive a different query plan, however, the query still runs very slowly. It does seem faster though. Initially, the query would run and I would lose patience and kill it after about 5 minutes. This time around, I think my answer came back in about 3-4 minutes.

Here's the sql I sent:

select /*+ PUSH_SUBQ */

       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 +0
   and al2.subject_id = al3.subject_id +0

   and al3.org_id = al4.org_id +0
   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

and here is the new query plan (I apologize if the tabbing/formatting is not coming through when I post this).

Query Plan

---

---

SELECT STATEMENT Cost = 1236762
  SORT GROUP BY
    NESTED LOOPS

      NESTED LOOPS
        NESTED LOOPS
          TABLE ACCESS FULL ORG
            COUNT STOPKEY
              VIEW
                SORT ORDER BY STOPKEY
                  SORT GROUP BY
                    HASH JOIN
                      TABLE ACCESS FULL ORG
                      HASH JOIN
                        TABLE ACCESS FULL SUBJECT
                        HASH JOIN
                          TABLE ACCESS FULL DATASTAGE
                          TABLE ACCESS FULL HIT
          TABLE ACCESS BY INDEX ROWID SUBJECT
            INDEX RANGE SCAN SUBJECT_ORG_ID_IDX
        TABLE ACCESS BY INDEX ROWID HIT
          INDEX RANGE SCAN HIT_SUBJ_ID_IDX

Query Plan

---

---

      TABLE ACCESS BY INDEX ROWID DATASTAGE
        INDEX RANGE SCAN DATASTAGE_UQ_ID_IDX

To give you some more information as to the relative sizes of the tables...the hit table and datastage table are the largest tables in this query. Datastage would be the biggest at about 200,000 rows and hit at about 160,000 rows. Certainly full table scans against those tables are not good.

Also, I've never seen the tactic of adding '+ 0' or concatenating null to columns in joins. I always thought that expressions in the join clause would effectively disable the indexes (unless of course you're using function based indexes). I know I've picked your brain enough about this tuning question, but can you briefly explain why this tactic in the join clause works?

Thanks for all the help. I appreciate it.

Gavin

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