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?
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 +0and al3.org_id = al4.org_id +0
(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 )
and here is the new query plan (I apologize if the tabbing/formatting is not coming through when I post this).
Query Plan
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