does Cartesian product happens each time we execute joins

Posted on 2008-11-14
Last Modified: 2013-12-19
Hi guys

Does the oracle always does the cartesian product before filtering out the records based on join condition

For example
      select * from category_new, category_temp where
     category_new.category = category_temp.category

what will happen in this case will the oracle behind the scene fetch all the records from both the tables, then perform cartesian product and then apply equi join.

Is this the case that oracle will perform cartesian product each time we ask for join ?
Question by:rajannagpal
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    the answer is: it depends.

    functionally, yes. however, technically, depending on the indexes you have on the tables, it will perform either a index-based row-lookup, a merge join, etc.

    run the explain plan for the query, and you will know.

    Author Comment

    I run the explain plan and reach the conclusion that oracle always perform cartesian product. I was suspicious about the result that why i need to verify.  

    so question is if we dont have indexes and assume we are going by book then what will happen, will it perform the cartesian product ?
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    this is why indexes are crutial, the bigger the table, the more important they are.
    LVL 27

    Accepted Solution

    >> Does the oracle always does the cartesian product

    It depends on how you look at a cartesian product of two sets.
    Cartesian product is a conditionless join between two sets. But, to process a query oracle doesn't do that.
    Oracle uses join methods like NESTED_LOOP, HASH_JOIN, SORT_MERGE_JOIN etc.
    Where, oralce DOESN'T compute a cartesian join initially and then apply the predicates, Instead the predicates are applied first and then the sort method gets you the required tuples.

    In most cases one dataset is taken as the driving dataset and the second dataset is scanned for the matches. It is not a cartesian join.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now