Hi All,
Please help me in understanding how joins work with in-line views.
I have a query and its explain plan as below:
SELECT e.ename,e.deptno,d.dname FROM
dept d,
emp e
WHERE e.deptno=d.deptno
AND e.deptno=20
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 210 | 11 |
| 1 | HASH JOIN | | 5 | 210 | 11 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 22 | 5 |
| 3 | TABLE ACCESS FULL| EMP | 5 | 100 | 5 |
-----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
I read the docuemnt at: http://www.dba-oracle.com/oracle_tips_rittman_inlineviews.htm,
section "Using outer joins with in-line views", under which he demonstartes the use of in-line views.
I changed the above query to use in-line view.
The new query and its explain plan are as below:
SELECT e.ename,e.deptno,d.dname FROM
dept d,
(SELECT * FROM emp WHERE deptno=20) e
WHERE e.deptno=d.deptno
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 210 | 11 |
| 1 | HASH JOIN | | 5 | 210 | 11 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 22 | 5 |
| 3 | TABLE ACCESS FULL| EMP | 5 | 100 | 5 |
-----------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
I do not find any difference in both the explain plans. Both are same.
In my second query, the filtered rows will be joined to dept table. And hence the baggage will reduce.
But how can I verify that in-line view has worked better?
Regards,
Lakshmi.
[Updated on: Mon, 03 December 2012 00:55]
Report message to a moderator
] 
Members
Search
Help
Register
Login
Home
