Quote:
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.
dBforums
>
SQL statement comparison
08-06-03, 06:04
SQL statement comparison
Linear Mode
