does Cartesian product happens each time we execute joins

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 ?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
rajannagpalAuthor Commented:
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 ?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is why indexes are crutial, the bigger the table, the more important they are.
SujithData ArchitectCommented:
>> 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.