joins-nested loops,hash or merge
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: joins-nested loops,hash or merge

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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?

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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 View Post
    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?
    http://www.perf-engg.com
    A performance engineering forum

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by malay_biswal View Post
    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;
    Last edited by mahajanakhil198; 02-17-2009 at 05:46 AM.

  8. #8
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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 View Post
    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
    http://www.perf-engg.com
    A performance engineering forum

  9. #9
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by malay_biswal View Post
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Guys this kid wants to learn. Anybody there..
    http://www.perf-engg.com
    A performance engineering forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width