1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle SQL Tuning

Discussion in 'SQL PL/SQL' started by sabyasachi, Oct 9, 2009.

  1. sabyasachi

    sabyasachi Active Member

    Messages:
    6
    Likes Received:
    2
    Trophy Points:
    65
    USE EXISTS IN PLACE OF DISTINCT
    visit for more information "allmyneed.blogspot . com"

    The DISTINCT combination of deptno and dname columns have to be displayed. Only those rows need be displayed, whose deptno exists in the EMP table.

    Query 1
    Code (SQL):

    SELECT DISTINCT E.deptno,D.dname
    FROM emp E,dept D
    WHERE D.deptno = E.deptno;


    OPERATION   OPTIONS OBJECT_NAME COST
    SELECT STATEMENT   
       
        14659
    SORT    UNIQUE 
        14659
    HASH JOIN  
       
        1000
    TABLE ACCESS    FULL    DEPT    1
    TABLE ACCESS    FULL    EMP 822
     
    Query 2
    Code (SQL):

    SELECT D.deptno,D.dname
    FROM dept D
    WHERE EXISTS ( SELECT 'X'
    FROM emp E
    WHERE E.deptno = D.deptno );

    OPERATION   OPTIONS OBJECT_NAME COST
    SELECT STATEMENT   
       
        1
    FILTER 
       
       
    TABLE ACCESS    FULL    DEPT    1
    TABLE ACCESS    FULL    EMP1    822
    Inference

    There is a reduction of more than 97% in estimated cost.In the query using the DISTINCT clause, the explain plan shows that there is a HASH join between the two tables. In this, the smaller of the two tables (in this case the DEPT table) is converted into a hash table and stored in memory. For every row of the larger table (EMP table) retrieved, the hash table is scanned to check for the join condition.
    The query using EXISTS uses a FILTER operation. The FILTER operation accepts a set of rows, eliminates some of them, and returns the rest. In the above case, the rows from the DEPT and EMP table are filtered out based on the join condition. EXISTS is a faster and a cheaper alternative because the optimizer realizes that when the sub-query has been satisfied once, there is no need to proceed any further, and the next driving row can be fetched. In the example shown only one row needs to be returned from dept even though many rows in emp might match the subquery.
     
  2. ram50958

    ram50958 Guest

    awesome information.
    Ram,