Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> nested loops and cartesian products
helpme42
post May 16 2010, 09:32 PM
Post #1


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



Could you let me know if the following is correct for the subsiquent code and explain plan:


a hash join is used to link availability.adate with requirements.rdate and requirements.wardshiftid
with wardshift.wardshiftid but then a series of nested loops are used to probe for person.personid
where it matches personID in the rows resulting from the hash join. However a Cartesian product
operation is caused possibly by a lack of join conditions in the last 5 lines of code:

Yes a have read my backside off to come to this conclusion, but is it correct?



CODE
SELECT TBLPERSON.SNAME, TBLPERSON.FNAME,
to_char(TBLWARDSHIFTS.SHIFTSTART,'HH24:MI') as ReqStart,
to_char(TBLWARDSHIFTS.SHIFTEND,'HH24:MI') as ReqEnd,
TBLREQUIREMENTS.RDATE
FROM TBLAVAILABILITY , TBLREQUIREMENTS , TBLWARDSHIFTS , TBLPERSON
WHERE TBLAVAILABILITY.ADATE = TBLREQUIREMENTS.RDATE
AND TBLAVAILABILITY.PERSONID = TBLPERSON.PERSONID
AND TBLREQUIREMENTS.WARDSHIFTID = TBLWARDSHIFTS.WARDSHIFTID
AND (to_char(TBLREQUIREMENTS.RDATE,'MM')=to_char(SYSDATE,'MM'))
AND (
      (To_Number(to_char(TBLWARDSHIFTS.SHIFTSTART,'HH24'))>=15)
      AND
      (To_Number(to_char(TBLWARDSHIFTS.SHIFTSTART,'HH24'))<20)
      AND (TBLAVAILABILITY.ANYLATE=1)
     )




CODE
  1 |    NESTED LOOPS                |                 |       |       |       |            |          |                                                                                                            
|  2 |     NESTED LOOPS               |                 |   233 | 15611 |       |  1469   (2)| 00:00:18 |
|* 3 |      HASH JOIN                 |                 |   233 | 10951 |       |  1003   (3)| 00:00:13 |
|* 4 |       TABLE ACCESS FULL        | TBLREQUIREMENTS |  5031 | 60372 |       |   980   (3)| 00:00:12 |                                                                                                            
|  5 |       MERGE JOIN CARTESIAN     |                 |  3061 |   104K|       |    22   (0)| 00:00:01 |                                                                                                            
|* 6 |        TABLE ACCESS FULL       | TBLWARDSHIFTS   |     1 |    20 |       |     3   (0)| 00:00:01 |                                                                                                            
|  7 |        BUFFER SORT             |                 |  4639 | 69585 |       |    19   (0)| 00:00:01 |                                                                                                            
|* 8 |         TABLE ACCESS FULL      | TBLAVAILABILITY |  4639 | 69585 |       |    19   (0)| 00:00:01 |                                                                                                            
|* 9 |      INDEX UNIQUE SCAN         | PK5             |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                            
|  10 |     TABLE ACCESS BY INDEX ROWID| TBLPERSON       |     1 |    20 |       |     2   (0)| 00:00:01


CODE
   3 - access("TBLAVAILABILITY"."ADATE"="TBLREQUIREMENTS"."RDATE" AND
"TBLREQUIREMENTS"."WARDSHIFTID"="TBLWARDSHIFTS"."WARDSHIFTID")
  4 - filter(TO_CHAR(INTERNAL_FUNCTION("TBLREQUIREMENTS"."RDATE"),'MM')=TO_CHAR(SYSDATE@!,'MM'))
  6 - filter(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("TBLWARDSHIFTS"."SHIFTSTART"),'HH24'))>=15 AND
TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("TBLWARDSHIFTS"."SHIFTSTART"),'HH24'))<20)
  8 - filter("TBLAVAILABILITY"."ANYLATE"=1)
  9 - access("TBLAVAILABILITY"."PERSONID"="TBLPERSON"."PERSONID")
Go to the top of the page
 
+Quote Post
Steve
post May 16 2010, 09:46 PM
Post #2


Advanced Member
***

Group: Members
Posts: 618
Joined: 22-January 06
From: Virginia Beach, VA
Member No.: 3,560



It looks like all your join conditions are satisfied here:

WHERE TBLAVAILABILITY.ADATE = TBLREQUIREMENTS.RDATE
AND TBLAVAILABILITY.PERSONID = TBLPERSON.PERSONID
AND TBLREQUIREMENTS.WARDSHIFTID = TBLWARDSHIFTS.WARDSHIFTID

This area is not conditional so it should work just fine. So, answer the following:

1. Are you joining in all cases from a foreign key to a primary key (the primary key being the most important)
2. If so, are you sure you are joining on the entire key? Perhaps one of the tables has a multi-column primary key and you are leaving a part out.

Check out these links:

http://www.dba-oracle.com/t_merge_join_cartesian.htm
http://www.dba-oracle.com/t_sql_merge_join_cartesian.htm

Just a quick note...sorry if I messed up your formatting, but I had to edit your initial post. When you post code from a SPOOL, make sure you do:

set trimspool on

That way the spaces from "set linesize" don't show up as well.


--------------------
Steve Karam
Sr. Consultant - Burleson Consulting
Oracle 10g Certified Master / Oracle ACE
http://www.OracleAlchemist.com
steve@orcldba.com
Go to the top of the page
 
+Quote Post
helpme42
post May 16 2010, 09:52 PM
Post #3


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



QUOTE (Steve @ May 16 2010, 09:47 PM) *
It looks like all your join conditions are satisfied here:

WHERE TBLAVAILABILITY.ADATE = TBLREQUIREMENTS.RDATE
AND TBLAVAILABILITY.PERSONID = TBLPERSON.PERSONID
AND TBLREQUIREMENTS.WARDSHIFTID = TBLWARDSHIFTS.WARDSHIFTID

This area is not conditional so it should work just fine. So, answer the following:

1. Are you joining in all cases from a foreign key to a primary key (the primary key being the most important)
2. If so, are you sure you are joining on the entire key? Perhaps one of the tables has a multi-column primary key and you are leaving a part out.

Check out these links:

http://www.dba-oracle.com/t_merge_join_cartesian.htm
http://www.dba-oracle.com/t_sql_merge_join_cartesian.htm

Just a quick note...sorry if I messed up your formatting, but I had to edit your initial post. When you post code from a SPOOL, make sure you do:

set trimspool on

That way the spaces from "set linesize" don't show up as well.


there are no foreign keys at this point in the database.

Am i correct in what I have said about the use of nested loops?
Go to the top of the page
 
+Quote Post
Steve
post May 16 2010, 11:11 PM
Post #4


Advanced Member
***

Group: Members
Posts: 618
Joined: 22-January 06
From: Virginia Beach, VA
Member No.: 3,560



Shame I have lost the original formatting. wink.gif With the formatting as it is, it looks like:

1. A full table scan of TBLREQUIREMENTS is done
2. A full table scan of TBLWARDSHIFTS is joined via a merge join cartesian with a sorted full table scan of TBLAVAILABILITY
3. The results of 1 and 2 are joined via hash join
4. The result of #3 is used as a driving table in a nested loop on the PK5 unique index on TBLPERSON
5. The final results including the rowid from 4 are used to get data from the PERSON table by ROWID.


--------------------
Steve Karam
Sr. Consultant - Burleson Consulting
Oracle 10g Certified Master / Oracle ACE
http://www.OracleAlchemist.com
steve@orcldba.com
Go to the top of the page
 
+Quote Post
burleson
post May 17 2010, 08:04 AM
Post #5


Advanced Member
***

Group: Members
Posts: 11,435
Joined: 26-January 04
Member No.: 13



Hi,

>> there are no foreign keys at this point in the database.

That's a worst practice, and I've seen DBA's get fired for such a major FUBAR . . .

Read this carefuly:

http://www.dba-oracle.com/t_merge_join_cartesian.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
helpme42
post May 17 2010, 10:36 AM
Post #6


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



QUOTE (burleson @ May 17 2010, 08:05 AM) *
Hi,

>> there are no foreign keys at this point in the database.

That's a worst practice, and I've seen DBA's get fired for such a major FUBAR . . .

Read this carefuly:

http://www.dba-oracle.com/t_merge_join_cartesian.htm


I have my reasons Burleson... I have my reasons smile.gif
Go to the top of the page
 
+Quote Post
burleson
post May 17 2010, 10:42 AM
Post #7


Advanced Member
***

Group: Members
Posts: 11,435
Joined: 26-January 04
Member No.: 13



>> I have my reasons Burleson... I have my reasons

And by your own admission, you are totally clueless about Oracle.

You need to chose another forum, that was rude, even by British standards.


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 2nd September 2014 - 11:35 PM