Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    153

    Unanswered: Pl/Sql Performance....

    Case 1:

    Select Ename,Job
    from Emp
    where Deptno IN ( Select Deptno from Dept)

    Case 2:

    Select Ename,Job
    from Emp
    where EXISTS ( Select Deptno From Dept)

    I have found the Case 2 takes less to time to perform.

    I would like to know the reason.
    I would appreciate any comments on this.

    ThankX

    JD

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,
    I think both the queries are different.
    Second query is missing join

    It should be

    Select Ename,Job
    from Emp
    where EXISTS ( Select Deptno From Dept where emp.deptno=dept.deptno)

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The reason would be different execution plans - run with AUTOTRACE ON to see the difference.

    Actually, as written these queries will produce different results anyway. Presumably you meant to correlate the EXISTS subquery like this:

    Select Ename,Job
    from Emp
    where EXISTS ( Select Deptno From Dept where Dept.deptno = Emp.deptno)

    This is what I get:
    Code:
    SQL> set autotrace traceonly
    SQL> Select Ename,Job
      2  from Emp
      3  where Deptno IN ( Select Deptno from Dept)
      4  /
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=280)
       1    0   NESTED LOOPS (Cost=1 Card=14 Bytes=280)
       2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=238)
       3    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
    
    
    
    
    Statistics
    ----------------------------------------------------------
            778  recursive calls
              4  db block gets
            168  consistent gets
             31  physical reads
              0  redo size
            439  bytes sent via SQL*Net to client
            307  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
             14  sorts (memory)
              0  sorts (disk)
             14  rows processed
    
    SQL> Select Ename,Job
      2  from Emp
      3* where EXISTS ( Select Deptno From Dept where Dept.deptno = Emp.deptno)
    SQL> /
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=17)
       1    0   FILTER
       2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=1 Bytes=17)
       3    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) (Cost=1 Card=1
               Bytes=3)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              4  db block gets
              5  consistent gets
              0  physical reads
              0  redo size
            452  bytes sent via SQL*Net to client
            307  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
             14  rows processed
    As you can see, the optimizer approached the 2 statements differently.

Posting Permissions

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