Outer join -> Union
Date: Thu, 4 Nov 2010 09:33:56 +0000
Message-ID: <09011014EB621E4CBC2536B62A1B6407701E_at_SMXC001.trivadis.com>
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--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 04 2010 - 04:33:56 CDT