DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: TIM HALL extract - CROSS JOIN

  1. #1
    Join Date
    Dec 2001
    Posts
    221
    what is the advantage of CROSS JOIN over the normail cartesian product.

    below is the plan of two statements ( from Tims's site ) . both shows the same plan.


    12:09:37 SQL> SELECT first_name,last_name, department_name FROM employees CROSS JOIN departments;
    Elapsed: 00:00:00.00

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=2889 Bytes=7
    8003)

    1 0 MERGE JOIN (CARTESIAN) (Cost=28 Card=2889 Bytes=78003)
    2 1 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=1 Card=27 Byt
    es=324)

    3 1 BUFFER (SORT) (Cost=27 Card=107 Bytes=1605)
    4 3 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=107 By
    tes=1605)




    12:09:43 SQL> SELECT first_name,last_name, department_name FROM employees, departments;
    Elapsed: 00:00:00.00

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=2889 Bytes=7
    8003)

    1 0 MERGE JOIN (CARTESIAN) (Cost=28 Card=2889 Bytes=78003)
    2 1 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=1 Card=27 Byt
    es=324)

    3 1 BUFFER (SORT) (Cost=27 Card=107 Bytes=1605)
    4 3 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=1 Card=107 By
    tes=1605)
    Santosh Jadhav
    8i OCP DBA

  2. #2
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,

    How many rows are there in both the tables.What I feel is when there is large amount of data then you would able to find the diffrence between both the queries.

    Take Care.

  3. #3
    Join Date
    Dec 2001
    Posts
    221
    both tables are the same created by sample schemas in Oracle 9i Release 1.

    any way, these are from HR schema

    employees - 107 records
    deparments - 27 records

    did you tried it ?
    Santosh Jadhav
    8i OCP DBA

  4. #4
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,

    No I didnt try but it would reflect the diffrence after having used for queries retriving large amount of data.

    Take Care.

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    There is no difference as far as I can tell. The point of a CROSS JOIN is to explicitly state that you are trying to get a cartesian product. That way people don't think you've made a mistake and try to fix it.

    Oracle claim that there are no performance differences between the traditional syntax and the ANSI syntax.

    The only time there is a difference is when using the FULL OUTER JOIN since this is not possible using the non-ANSI syntax.

    Note. Please don't start quoting examples of SQL with UNIONs. These are not examples of FULL OUTER JOINs, although they can be used to return the same data set that a FULL OUTER JOIN would.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  6. #6
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,

    but there is no performance increase compared to the existing syntax

    From Tim's Site...

    Take Care.

  7. #7
    Join Date
    Dec 2001
    Posts
    221
    Originally posted by TimHall

    Note. Please don't start quoting examples of SQL with UNIONs. These are not examples of FULL OUTER JOINs, although they can be used to return the same data set that a FULL OUTER JOIN would.

    Cheers
    can u explain it in detail .
    Santosh Jadhav
    8i OCP DBA

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    to start with union you have to run more than one time the same (or similar) query, with joins you DONT
    You cannot compare unions with joins man

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by sjadhavdba
    what is the advantage of CROSS JOIN over the normail cartesian product.
    Cross join is the equivalent of a Cartesian product. The only difference is that before 9i we could only implicitely ask for the Cartesian product of two tables by listing the tables in the FROM clause and then we had to fix the mess by using the WHERE clause. Now, full SQL:1999 joint syntax is being implemented and the join is given where it should be.

    I am just curious: is there a DBA who knows or is interested in infinite Cartesian products?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width