joins-nested loops,hash or merge

Q1:What are the factors that determine the join, viz. nested loop, hash or merge, to be used by a query?

not able to figure out the things as mentioned below:

SQL> create table e(name char(1), d_id number);

Table created.

SQL> create table d(d_id number, name char(1));

Table created.

SQL> select * from d,e where d.d_id=e.d_id;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'E'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'D'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


SQL> alter table d add primary key(d_id);

Table altered.

SQL> select * from d,e where d.d_id=e.d_id;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'E'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'D'
4 3 INDEX (UNIQUE SCAN) OF 'SYS_C003007' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>


Based on my reading of various googled topics, i felt that the nested loop join should have been used in the absence of indexes on d_id column of both tables and merge join should have been used in the presence of indexes on d_id column of both tables; and clueless abt when hash join to be used.

The misery doesnt end here.
Here is what i explored further.

SQL> alter table d drop primary key;

Table altered.

SQL> select * from d,e where d.d_id=e.d_id;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'E'
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'D'
4 2 INDEX (RANGE SCAN) OF 'E_TEST' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL> analyze table e compute statistics;

Table analyzed.

SQL> analyze table d compute statistics;

Table analyzed.

SQL> select * from d,e where d.d_id=e.d_id;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'E'
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=32)
3 2 TABLE ACCESS (FULL) OF 'D' (Cost=2 Card=1 Bytes=16)
4 2 INDEX (RANGE SCAN) OF 'E_TEST' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL> alter table d add primary key(d_id);

Table altered.

SQL> select * from d,e where d.d_id=e.d_id;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'E'
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=32)
3 2 TABLE ACCESS (FULL) OF 'D' (Cost=2 Card=1 Bytes=16)
4 2 INDEX (RANGE SCAN) OF 'E_TEST' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL> select * from e,d where e.d_id=d.d_id;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=32)
1 0 NESTED LOOPS (Cost=2 Card=1 Bytes=32)
2 1 TABLE ACCESS (FULL) OF 'E' (Cost=2 Card=1 Bytes=16)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'D'
4 3 INDEX (UNIQUE SCAN) OF 'SYS_C003008' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Q2. what determines the join order?
---ordering of tables in from clause(i figured, it is this factor; but still dont
want to be wrong at sm later stage...dats y asking)
---ordering of coumns in where clause

Q3: how do the following affect the performance of and type join to be used by a query?
---ordering of table names in the from clause
---order of columns in the where clause
---unique(primary key cloumn) and non-unique(foreign key column) indexes
on the columns being joined.

Go through docs. May be this doc helps. http://oracle-online-help.blogspot.c...ort-merge.html

If still you are not clear ask specific questions.

first of all thanks for dat link.It helped me get the basics right.
I still have few doubts:
1. Do the indexes on both joined key columns affect the type of join to be used?
---I've indexed both columns but still it is nested loops..Will it go on to use sort merge
join with larger data set?

2.How do indexes affect query's performance?

3. How does the join order affect query's performance?

1. It depends on how big are the tables. If tables are small or it's using FIRST_ROWS chances are more it'll use nested loop.
2. I would suggest to rad some basic indexing material. They affect query performance drastically.

3. Go through this.
http://www.dba-oracle.com/t_table_join_order.htm

Quote:

---

Originally Posted by mahajanakhil198

first of all thanks for dat link.It helped me get the basics right.
I still have few doubts:
1. Do the indexes on both joined key columns affect the type of join to be used?
---I've indexed both columns but still it is nested loops..Will it go on to use sort merge
join with larger data set?

2.How do indexes affect query's performance?

3. How does the join order affect query's performance?

---

actually i meant to ask
1.do the indexes affect the performance of join queries? If so,how?
---i mean the indexes on columns of driving table and driven table.
2. is there any difference between the following queries on performance grounds ?
select * from e,d where e.d_id=d.d_id;
select * from d,e where d.d_id=e.d_id;
---i was actually talking of this join order. sorry for not being descriptive.

Yes Indexes affect performance of joins. Assume this logic where
For (in 1 to 1000)
select salary from emp where emp_number:=B1
End loop

In this case the inner query will be accessed by emp_number index and query will be faster.
If in this case there is a index SKIP scan on emp table then that means emp_number is not the leading column on the index.
2. Usually it should not if the tables involved has correct stats gathered. If not then oracle makes mistake and in that case you can put ordered hint to force Oacle use your preferred order.

Quote:

---

Originally Posted by malay_biswal

Yes Indexes affect performance of joins. Assume this logic where
For (in 1 to 1000)
select salary from emp where emp_number:=B1
End loop

In this case the inner query will be accessed by emp_number index and query will be faster.
If in this case there is a index SKIP scan on emp table then that means emp_number is not the leading column on the index.

---

I think it doesnt explain this:
do the indexes affect the performance of join queries? If so,how?
---i mean the indexes on columns of driving table and driven table.

suppose i have indices test1 on column d_id of table e and test2 on column d_id of table d..
Then how will the following query use indices test1 and test2 in any way?
select * from d,e where e.d_id=d.d_id;

Do your self a favour. If it does not clear to you, then read documents. I've a job to do except explaining you everything.

Quote:

---

Originally Posted by mahajanakhil198

I think it doesnt explain this:
do the indexes affect the performance of join queries? If so,how?
---i mean the indexes on columns of driving table and driven table.

suppose i have indices test1 on column d_id of table e and test2 on column d_id of table d..
Then how will the following query use indices test1 and test2 in any way?
select * from d,e where e.d_id=d.d_id;

---

o and read

Quote:

---

Originally Posted by malay_biswal

Do your self a favour. If it does not clear to you, then read documents. I've a job to do except explaining you everything.


o and read

---

firstly thanks for the pain u had to bear in explainining to me. ur links were helpful.
secondly u r nt bound to answer me as this is not for wat u r paid. Here everybody does or tries to do favour sm other person. I put question becos sm has very rightly said "LITTLE KNOWLEDGE IS MORE HARMFUL THAN KNOWING NOTHING". Had i found it in any document or after googling, i wud have never posted this thread. I m at such a remote location where i dont find ppl around me to discuss. Though as of now, i m not of much help to ppl but i keep track of forums of dbasupport.com for learning purpose and this is what i really love abt this web portal.
lastly, i feel there r more ppl like me who r beginners or amateurs and who also willingly learn when smthing new is posted here.

Guys this kid wants to learn. Anybody there..