Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


does Cartesian product happens each time we execute joins

Posted on 2008-11-14
Medium Priority
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
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22963808
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

ID: 22963861
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 143

Expert Comment

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

Accepted Solution

sujith80 earned 150 total points
ID: 22966322
>> 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

564 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