Re: Outer join -> Union
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: Petr.Novak@xxxxxxxxxxxx
- Date: Thu, 04 Nov 2010 12:17:44 +0100
I'd try to compute statistics before playing with hints ...
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
On 11/04/2010 10:33 AM, Petr Novak wrote:
> 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
>
>
>
>
Other related posts:
