AW: Outer join -> Union
- From: Petr Novak <Petr.Novak@xxxxxxxxxxxx>
- To: Ric Van Dyke <ric.van.dyke@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Nov 2010 12:39:14 +0000
Hallo Ric,
I forgot to mention the DB version - it is 10.2.0.4 on Linux, statistics were
computed (without histograms). Original query takes 1.08 seconds, union 0.01
Original statement:
select * from table(dbms_xplan.display_cursor('a9cbapx2jx6v2',0,'ALL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID a9cbapx2jx6v2, child number 0
-------------------------------------
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'
Plan hash value: 2853649387
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 1978 (100)|
|
|* 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")
Child CPU/Ex Elaps/Ex Buffer/Ex DReads/Ex Executions Opt.
Cost Sorts Rows Username PlanHash Last Load
------ ---------- ----------- ------------- ------------- -----------
---------- ---------- --------- -------------------- -------------
--------------
0 1.08 1.08 6771.0 .0 1
1978 0 51 SYS 2853649387 11-04/13:13:09
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 1.93 1.89 0 6656 0 51
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 1.94 1.89 0 6656 0 51
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
51 FILTER (cr=6656 pr=0 pw=0 time=720778 us)
1583976 HASH JOIN OUTER (cr=6656 pr=0 pw=0 time=2300778 us)
800000 TABLE ACCESS FULL T (cr=3326 pr=0 pw=0 time=800131 us)
800000 TABLE ACCESS FULL T (cr=3330 pr=0 pw=0 time=800093 us)
###############################################################
Second statement , with union
select * from table(dbms_xplan.display_cursor('3h8h7yw5yzr7p',0,'ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3h8h7yw5yzr7p, child number 0
-------------------------------------
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'
Plan hash value: 4270335634
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 16
(100)| |
| 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")
Child Nr CPU/Ex Elaps/Ex Buffer/Ex DReads/Ex Executions Opt. Cost
Sorts Rows Username PlanHash Last Load
-------- --------- --------- ------------- ------------- ----------- ---------
---------- --------- -------------------- ------------- --------------
0 .01 .01 16.0 .0 1 16
2 51 SYS 4270335634 11-04/13:15:06
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 16 0 51
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 16 0 51
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
51 SORT UNIQUE (cr=16 pr=0 pw=0 time=570 us)
51 UNION-ALL (cr=16 pr=0 pw=0 time=580 us)
50 MERGE JOIN OUTER (cr=8 pr=0 pw=0 time=418 us)
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=72 us)
1 INDEX RANGE SCAN T_ID_IDX (cr=3 pr=0 pw=0 time=51 us)(object id
63295)
50 BUFFER SORT (cr=4 pr=0 pw=0 time=291 us)
50 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=336 us)
50 INDEX RANGE SCAN T_ID2_IDX (cr=3 pr=0 pw=0 time=126 us)(object id
63297)
1 TABLE ACCESS BY INDEX ROWID T (cr=8 pr=0 pw=0 time=107 us)
3 NESTED LOOPS (cr=7 pr=0 pw=0 time=131 us)
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=51 us)
1 INDEX RANGE SCAN T_ANAME_IDX (cr=3 pr=0 pw=0 time=36 us)(object
id 63296)
1 INDEX RANGE SCAN T_ID_IDX (cr=3 pr=0 pw=0 time=18 us)(object id
63295)
Best Regards,
Petr
Betreff: RE: Outer join -> Union
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:
