Hi,
My Oracle DB : 11G
Purpose of this question : Self Learning
suppose I have a query with 10 tables
Tables: A,B,C,D,E,F,G,H,I,K,L
More information:
a) there is an equi join between the tables(every row in every table has a match)
b) Table A is the driving table with 1 million rows
c) Rest of the tables, these are lookup tables and every table has less than 10 rows, and all these tables join with the driving table, like A.c1 = B.c1 and A.c2 = C.C1 and A.c3 = D.C1 and so on..
Now the question:
Lets say that I have fired this query at a time when there are huge number of users logged in and the resources are highly consumed, further I just want to know how the optimizer join these tables, like:
scenario 1: Oracle reads Table A and joins with with Table B for all rows and then A and B resultset gets joined with table C and so on for the rest of the tables..
Or
Scenario 2: Oracle reads 1st row from Table A and looks up all the tables , then reads row 2 from Table A and joins with all the lookup tables and so on ..
Just a conceptual question that's ringing in my head, Just wanted someone from tuning background to throw some light on this, Please let me know if i am ambiguous anywhere, I tried to explain every part of my question as thoroughly as possible.
Thanks
Rahul