nested loops and cartesian products Options

[helpme42]

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")

[Steve]

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.

[helpme42]

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?

[Steve]

Shame I have lost the original formatting. 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.

[burleson]

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

[helpme42]

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

[burleson]

>> 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.