Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Arrow Unanswered: SQL statement comparison

    Hi all,

    I have two sql statements.

    SQL 1
    ------
    Code:
    select tab1.col1, tab2.col2
    from tab1, tab2
    where tab1.col1 = tab2.col1
    SQL 2
    ------
    Code:
    select tab1.col1, v.col2
    from tab1, (select col1, col2 from tab2) v
    where tab1.col1 = v.col1
    Assume both tab1 and tab2 have col1 and col2.

    The 2 sql statements above return exactly the same result. What's the difference between them? Which is better in terms of performance?

    Thanks in advance.
    Cheers,
    Shev

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL statement comparison

    Originally posted by shev
    Hi all,

    I have two sql statements.

    SQL 1
    ------
    Code:
    select tab1.col1, tab2.col2
    from tab1, tab2
    where tab1.col1 = tab2.col1
    SQL 2
    ------
    Code:
    select tab1.col1, v.col2
    from tab1, (select col1, col2 from tab2) v
    where tab1.col1 = v.col1
    Assume both tab1 and tab2 have col1 and col2.

    The 2 sql statements above return exactly the same result. What's the difference between them? Which is better in terms of performance?

    Thanks in advance.
    Logically they are the same, and in all probability they will have same performance. Use EXPLAIN PLAN to see if the execution plans are the same or different. If they are different, performance may be different. Oracle's cost-based optimizer is good at rewriting queries, merging view definitions into the query etc. However, sometimes the way a query is written can force Oracle into a sub-optimal plan, so go for the simplest query generally.

    Here is the result of a similar pair of queries:

    Code:
    SQL> set autotrace traceonly
    SQL> select emp.ename, dept.dname
      2  from emp, dept
      3  where emp.deptno = dept.deptno;
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=280)
       1    0   HASH JOIN (Cost=3 Card=14 Bytes=280)
       2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=52)
       3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=98)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              8  db block gets
              3  consistent gets
              0  physical reads
              0  redo size
            689  bytes sent via SQL*Net to client
            508  bytes received via SQL*Net from client
              4  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             14  rows processed
    
    SQL> select emp.ename, v.dname
      2  from emp, (select dept.deptno, dept.dname from dept) v
      3  where emp.deptno = v.deptno;
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=14 Bytes=280)
       1    0   HASH JOIN (Cost=3 Card=14 Bytes=280)
       2    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=4 Bytes=52)
       3    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=98)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              8  db block gets
              3  consistent gets
              0  physical reads
              0  redo size
            690  bytes sent via SQL*Net to client
            542  bytes received via SQL*Net from client
              4  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             14  rows processed
    So no difference in this case.

  3. #3
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Thumbs up

    Thank you for your detailed explaination!

    Is it normally we use EXPLAIN PLAN to tune sql statements? Any good resources?
    Cheers,
    Shev

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by shev
    Thank you for your detailed explaination!

    Is it normally we use EXPLAIN PLAN to tune sql statements? Any good resources?
    Yes, that is one of the main Oracle SQL tuning methods. Another is SQL Trace and TKPROF. They are both described here:

    http://technet.oracle.com/docs/produ...a96533/toc.htm

    There are various books on the market - search any online bookseller for Oracle and Performance - but as I don't possess any of them, I can't make any specific recommendation. Well, I always recommend "Expert One-on-One Oracle" by Tom Kyte, which covers these topics along with dozens of others.

  5. #5
    Join Date
    Jul 2003
    Location
    Hong Kong
    Posts
    56

    Talking

    Thank you very much andrewst!!

    I'll take a look at the book you recommend.
    Cheers,
    Shev

Posting Permissions

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