RE: Outer join -> Union
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <Petr.Novak@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Nov 2010 06:38:32 -0500
What are the run time stats of running the outer join and the union?
What do you mean by "significantly better"? Can you trace (10046) the
runs and post the stat lines of each plan?
-----------------------
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium
March 6 - 10, 2011
You have to be there, yea I'm talking to you.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Petr Novak
Sent: Thursday, November 04, 2010 5:34 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Outer join -> Union
Hallo ,
I try to replace Left outer join (with Full Scans) to replace with
Union.
create table t (id number,aname varchar2(32),bname varchar2(32),id2
number);
insert into t
select rownum ,
'a'||to_char(rownum),'b'||to_char(rownum),round(rownum/50)
from dual connect by rownum<=800000;
create index t_id_idx on t(id);
create index t_aname_idx on t(aname);
create index t_id2_idx on t(id2);
select t1.id,t1.aname,t2.id2,t2.bname
from t t1 left outer join t t2 on t1.id=t2.id2
where t1.id=120 or t2.aname='a12345';
Execution Plan
----------------------------------------------------------
Plan hash value: 2853649387
------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost
(%CPU)| Time |
------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 2 | 58 | | 1978
(11)| 00:00:06 |
|* 1 | FILTER | | | | |
| |
|* 2 | HASH JOIN OUTER | | 2 | 58 | 17M| 1978
(11)| 00:00:06 |
| 3 | TABLE ACCESS FULL| T | 800K| 8593K| | 408
(12)| 00:00:02 |
| 4 | TABLE ACCESS FULL| T | 800K| 13M| | 418
(14)| 00:00:02 |
------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."ID"=120 OR "T2"."ANAME"='a12345')
2 - access("T1"."ID"="T2"."ID2"(+))
Following query returns the same results, but is significantly better.
select t1.id,t1.aname,t2.id2,t2.bname
from t t1 left outer join t t2 on t1.id=t2.id2
where t1.id=120
union
select t1.id,t1.aname,t2.id2,t2.bname
from t t1 left outer join t t2 on t1.id=t2.id2
where t2.aname='a12345';
------------------------------------------------------------------------
-----------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
------------------------------------------------------------------------
-----------------------
| 0 | SELECT STATEMENT | | 51 | 1129 |
16 (57)| 00:00:01 |
| 1 | SORT UNIQUE | | 51 | 1129 |
16 (57)| 00:00:01 |
| 2 | UNION-ALL | | | |
| |
| 3 | MERGE JOIN OUTER | | 50 | 1100 |
7 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | T | 1 | 11 |
4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_ID_IDX | 1 | |
3 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 50 | 550 |
3 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| T | 50 | 550 |
3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | T_ID2_IDX | 50 | |
2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | T | 1 | 11 |
3 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 29 |
7 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| T | 1 | 18 |
4 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | T_ANAME_IDX | 1 | |
3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | T_ID_IDX | 1 | |
2 (0)| 00:00:01 |
------------------------------------------------------------------------
-----------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."ID"=120)
8 - access("T2"."ID2"(+)=120)
12 - access("T2"."ANAME"='a12345')
13 - access("T1"."ID"="T2"."ID2")
It is possible to force the original query not to use the FULL Scans ? I
tried to play with CONCAT and INDEX Hints, but with no success.
Best Regards,
Petr--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: