Tuning Left join:
SQL> /
已选择1777行。
已用时间: 00: 00: 15.82
执行计划
----------------------------------------------------------
Plan hash value: 3132434474
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102M| 23G| | 23561 (98)| 00:04:43 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 102M| 23G| | 23561 (98)| 00:04:43 |
|* 3 | HASH JOIN | | 102M| 23G| | 2407 (75)| 00:00:29 |
|* 4 | TABLE ACCESS FULL | A | 2790 | 514K| | 156 (2)| 00:00:02 |
|* 5 | HASH JOIN RIGHT OUTER| | 515K| 25M| 1016K| 486 (4)| 00:00:06 |
| 6 | TABLE ACCESS FULL | B | 41410 | 525K| | 155 (2)| 00:00:02 |
| 7 | TABLE ACCESS FULL | A | 46902 | 1786K| | 156 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."CREATED">=MIN("A"."CREATED"))
3 - access("A"."OWNER"="A"."OWNER")
4 - filter("A"."OBJECT_TYPE"='INDEX')
5 - access("A"."OBJECT_ID"="B"."DATA_OBJECT_ID"(+))
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2091 consistent gets 0 physical reads
0 redo size
116065 bytes sent via SQL*Net to client
1683 bytes received via SQL*Net from client
120 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1777 rows processed
SQL> l
1 select *
2 from a,
3 (select a.owner, min(a.created) created
4 from a
5 left outer join b on a.object_id = b.data_object_id 6 group by a.owner) temp
7 where a.owner = temp.owner
8 and a.object_type = 'INDEX'
9* and a.created >= temp.created
我改后:
SQL> l
1 select *
2 from a,
3 (select a.owner, min(a.created) created
4 from a,b
5 where a.object_id = b.data_object_id(+) 6 group by a.owner) temp
7 where a.owner = temp.owner
8 and a.object_type = 'INDEX'
9* and a.created >= temp.created
SQL> /
已选择1777行。
已用时间: 00: 00: 00.12
执行计划
----------------------------------------------------------
Plan hash value: 3851281404
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 467K| 90M| | 802 (23)| 00:00:10 |
|* 1 | HASH JOIN | | 467K| 90M| | 802 (23)| 00:00:10 |
|* 2 | TABLE ACCESS FULL | A | 2790 | 482K| | 156 (2)| 00:00:02 |
| 3 | VIEW | | 46902 | 1190K| | 485 (4)| 00:00:06 |
| 4 | HASH GROUP BY | | 46902 | 2381K| | 485 (4)| 00:00:06 |
|* 5 | HASH JOIN RIGHT OUTER| | 46902 | 2381K| 1016K| 478 (2)| 00:00:06 |
| 6 | TABLE ACCESS FULL | B | 41410 | 525K| | 155 (2)| 00:00:02 |
| 7 | TABLE ACCESS FULL | A | 46902 | 1786K| | 156 (2)| 00:00:02 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OWNER"="TEMP"."OWNER")
filter("A"."CREATED">="TEMP"."CREATED")
2 - filter("A"."OBJECT_TYPE"='INDEX')
5 - access("A"."OBJECT_ID"="B"."DATA_OBJECT_ID"(+))
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2091 consistent gets 0 physical reads
0 redo size
88024 bytes sent via SQL*Net to client
1683 bytes received via SQL*Net from client
120 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1777 rows processed
Tuning Right join:
SQL> /
已选择1694行。
已用时间: 00: 00: 01.50
执行计划
----------------------------------------------------------
Plan hash value: 1064154308
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 102M| 23G| | 23561 (98)| 00:04:43 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 102M| 23G| | 23561 (98)| 00:04:43 |
|* 3 | HASH JOIN | | 102M| 23G| | 2407 (75)| 00:00:29 |
|* 4 | TABLE ACCESS FULL | A | 2790 | 514K| | 156 (2)| 00:00:02 |
|* 5 | HASH JOIN | | 515K| 25M| 1016K| 486 (4)| 00:00:06 |
| 6 | TABLE ACCESS FULL| B | 41410 | 525K| | 155 (2)| 00:00:02 |
| 7 | TABLE ACCESS FULL| A | 46902 | 1786K| | 156 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."CREATED">=MIN("A"."CREATED"))
3 - access("A"."OWNER"="A"."OWNER")
4 - filter("A"."OBJECT_TYPE"='INDEX')
5 - access("A"."OBJECT_ID"="B"."DATA_OBJECT_ID")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2091 consistent gets
0 physical reads
0 redo size
110465 bytes sent via SQL*Net to client
1617 bytes received via SQL*Net from client
114 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1694 rows processed
SQL> select *
2 from a,
3 (select a.owner, min(a.created) created
4 from a
5 right outer join b on a.object_id = b.data_object_id
6 group by a.owner) temp
7 where a.owner = temp.owner
8 and a.object_type = 'INDEX'
9 and a.created >= temp.created
10
SQL> /
我改后:
SQL> /
已选择1694行。
已用时间: 00: 00: 00.10
执行计划
----------------------------------------------------------
Plan hash value: 1845623786
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 412K| 79M| | 783 (21)| 00:00:10 |
|* 1 | HASH JOIN | | 412K| 79M| | 783 (21)| 00:00:10 |
|* 2 | TABLE ACCESS FULL | A | 2790 | 482K| | 156 (2)| 00:00:02 |
| 3 | VIEW | | 41410 | 1051K| | 484 (4)| 00:00:06 |
| 4 | HASH GROUP BY | | 41410 | 2102K| | 484 (4)| 00:00:06 |
|* 5 | HASH JOIN OUTER | | 41410 | 2102K| 1016K| 478 (2)| 00:00:06 |
| 6 | TABLE ACCESS FULL| B | 41410 | 525K| | 155 (2)| 00:00:02 |
| 7 | TABLE ACCESS FULL| A | 46902 | 1786K| | 156 (2)| 00:00:02 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OWNER"="TEMP"."OWNER")
filter("A"."CREATED">="TEMP"."CREATED")
2 - filter("A"."OBJECT_TYPE"='INDEX')
5 - access("A"."OBJECT_ID"(+)="B"."DATA_OBJECT_ID")
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2091 consistent gets
0 physical reads
0 redo size
84027 bytes sent via SQL*Net to client
1617 bytes received via SQL*Net from client
114 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1694 rows processed
SQL> l
1 select *
2 from a,
3 (select a.owner, min(a.created) created
4 from a,b
5 where a.object_id(+) = b.data_object_id
6 group by a.owner) temp
7 where a.owner = temp.owner
8 and a.object_type = 'INDEX'
9* and a.created >= temp.created
Conclusion:
1 tun Outer Joins to + will be possible more efficient;
2 but it will be stands in the way of VIEW MERGE。
-----------end-----------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13750068/viewspace-721591/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13750068/viewspace-721591/