[20160318]push_pred hint使用疑惑.txt
--前几天看的帖子,链接如下:
http://www.itpub.net/thread-2054898-1-1.html
--当时的第一感觉,就是闭包传递的问题,做1个记录:
1.环境:
book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table T_SMALL_TABLE as select * from dba_objects;
create index I_T_SMALL_TABLE_OWNER on T_SMALL_TABLE (OWNER);
create table T_HUGE_TABLE as select * from dba_objects;
create index I_T_HUGE_TABLE_object_id on T_HUGE_TABLE (OBJECT_ID);
create table T_OTHER_TABLE as select * from dba_objects;
create index I_T_OTHER_TABLE_object_id on T_OTHER_TABLE (OBJECT_ID);
2.测试:
WITH vm
AS (SELECT *
FROM (SELECT t.*
,ROW_NUMBER ()
OVER
(
PARTITION BY t.object_id
ORDER BY t.CREATED DESC
)
rn
FROM t_huge_table t)
WHERE rn = 1)
SELECT *
FROM t_small_table a
JOIN vm ON a.object_id = vm.object_id
LEFT JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu';
Plan hash value: 1484884627
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 0 |00:00:00.01 | 2 | | | |
| 1 | NESTED LOOPS OUTER | | 1 | 1 | 456 | 6 (17)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 338 | 4 (25)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | T_SMALL_TABLE | 1 | 1 | 118 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | I_T_SMALL_TABLE_OWNER | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 5 | VIEW PUSHED PREDICATE | | 0 | 1 | 220 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 6 | WINDOW SORT PUSHED RANK | | 0 | 1 | 118 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
| 7 | TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE | 0 | 1 | 118 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 8 | INDEX RANGE SCAN | I_T_HUGE_TABLE_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | T_OTHER_TABLE | 0 | 1 | 118 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX RANGE SCAN | I_T_OTHER_TABLE_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- id=5 VIEW PUSHED PREDICATE ,很好地使用push_pred.但是当把left删除.看看执行计划:
WITH vm
AS (SELECT *
FROM (SELECT t.*
,ROW_NUMBER ()
OVER
(
PARTITION BY t.object_id
ORDER BY t.CREATED DESC
)
rn
FROM t_huge_table t)
WHERE rn = 1)
SELECT *
FROM t_small_table a
JOIN vm ON a.object_id = vm.object_id
JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu';
Plan hash value: 1375926145
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3091 (100)| | 0 |00:00:00.01 | 2 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 456 | | 3091 (1)| 00:00:38 | 0 |00:00:00.01 | 2 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 456 | | 3091 (1)| 00:00:38 | 0 |00:00:00.01 | 2 | | | |
|* 3 | HASH JOIN | | 1 | 1 | 338 | | 3089 (1)| 00:00:38 | 0 |00:00:00.01 | 2 | 795K| 795K| 181K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| T_SMALL_TABLE | 1 | 1 | 118 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | I_T_SMALL_TABLE_OWNER | 1 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 6 | VIEW | | 0 | 103K| 21M| | 3088 (1)| 00:00:38 | 0 |00:00:00.01 | 0 | | | |
|* 7 | WINDOW SORT PUSHED RANK | | 0 | 103K| 11M| 15M| 3088 (1)| 00:00:38 | 0 |00:00:00.01 | 0 | 13M| 1379K| |
| 8 | TABLE ACCESS FULL | T_HUGE_TABLE | 0 | 103K| 11M| | 347 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
|* 9 | INDEX RANGE SCAN | I_T_OTHER_TABLE_OBJECT_ID | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | T_OTHER_TABLE | 0 | 1 | 118 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--执行计划无法推入.实际上连接顺序并没有发生变化,为什么使用left join可以推入,而使用join不行呢?
--分析表以后看看.
Plan hash value: 974994052
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2655 (100)| | 0 |00:00:00.01 | 2 | | | |
|* 1 | HASH JOIN | | 1 | 492 | 199K| | 2655 (1)| 00:00:32 | 0 |00:00:00.01 | 2 | 724K| 724K| 174K (0)|
|* 2 | HASH JOIN | | 1 | 492 | 152K| | 2307 (1)| 00:00:28 | 0 |00:00:00.01 | 2 | 813K| 813K| 179K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| T_SMALL_TABLE | 1 | 492 | 48216 | | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | I_T_SMALL_TABLE_OWNER | 1 | 492 | | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 5 | VIEW | | 0 | 87032 | 18M| | 2291 (1)| 00:00:28 | 0 |00:00:00.01 | 0 | | | |
|* 6 | WINDOW SORT PUSHED RANK | | 0 | 87032 | 8329K| 11M| 2291 (1)| 00:00:28 | 0 |00:00:00.01 | 0 | 9370K| 1189K| |
| 7 | TABLE ACCESS FULL | T_HUGE_TABLE | 0 | 87032 | 8329K| | 347 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
| 8 | TABLE ACCESS FULL | T_OTHER_TABLE | 0 | 87034 | 8329K| | 347 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--依旧不行.
--删除 JOIN t_other_table c ON vm.object_id = c.object_id 在执行看看:
WITH vm
AS (SELECT *
FROM (SELECT t.*
,ROW_NUMBER ()
OVER
(
PARTITION BY t.object_id
ORDER BY t.CREATED DESC
)
rn
FROM t_huge_table t)
WHERE rn = 1)
SELECT *
FROM t_small_table a
JOIN vm ON a.object_id = vm.object_id
WHERE a.owner = 'kudfweu';
Plan hash value: 1354569996
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1492 (100)| | 0 |00:00:00.01 | 2 | | | |
| 1 | NESTED LOOPS | | 1 | 492 | 152K| 1492 (33)| 00:00:18 | 0 |00:00:00.01 | 2 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T_SMALL_TABLE | 1 | 492 | 48216 | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | I_T_SMALL_TABLE_OWNER | 1 | 492 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 4 | VIEW PUSHED PREDICATE | | 0 | 1 | 220 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 5 | WINDOW SORT PUSHED RANK | | 0 | 1 | 98 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
| 6 | TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE | 0 | 1 | 98 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 7 | INDEX RANGE SCAN | I_T_HUGE_TABLE_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$55EFA6E7
2 - SEL$55EFA6E7 / A@SEL$1
3 - SEL$55EFA6E7 / A@SEL$1
4 - SEL$B01C6807 / from$_subquery$_001@SEL$2
5 - SEL$B01C6807
6 - SEL$B01C6807 / T@SEL$3
7 - SEL$B01C6807 / T@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$B01C6807")
PUSH_PRED(@"SEL$55EFA6E7" "from$_subquery$_001"@"SEL$2" 2)
OUTLINE_LEAF(@"SEL$55EFA6E7")
MERGE(@"SEL$F5BB74E1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$55EFA6E7")
MERGE(@"SEL$F5BB74E1")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$55EFA6E7" "A"@"SEL$1" ("T_SMALL_TABLE"."OWNER"))
NO_ACCESS(@"SEL$55EFA6E7" "from$_subquery$_001"@"SEL$2")
LEADING(@"SEL$55EFA6E7" "A"@"SEL$1" "from$_subquery$_001"@"SEL$2")
USE_NL(@"SEL$55EFA6E7" "from$_subquery$_001"@"SEL$2")
INDEX_RS_ASC(@"SEL$B01C6807" "T"@"SEL$3" ("T_HUGE_TABLE"."OBJECT_ID"))
END_OUTLINE_DATA
*/
--强行推入呢?
WITH vm
AS (SELECT *
FROM (SELECT /*+ qb_name(x) */t.*
,ROW_NUMBER ()
OVER
(
PARTITION BY t.object_id
ORDER BY t.CREATED DESC
)
rn
FROM t_huge_table t)
WHERE rn = 1)
SELECT /*+ PUSH_PRED(@x) */ *
FROM t_small_table a
JOIN vm ON a.object_id = vm.object_id
JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu';
--问题依旧.执行计划如下:
Plan hash value: 560668266
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 128M(100)| | 0 |00:00:00.01 | 2 | | | |
| 1 | NESTED LOOPS | | 1 | 492 | 199K| 128M (34)|428:37:22 | 0 |00:00:00.01 | 2 | | | |
| 2 | MERGE JOIN CARTESIAN | | 1 | 42M| 8000M| 169K (1)| 00:34:00 | 0 |00:00:00.01 | 2 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | T_SMALL_TABLE | 1 | 492 | 48216 | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | I_T_SMALL_TABLE_OWNER | 1 | 492 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 5 | BUFFER SORT | | 0 | 87034 | 8329K| 169K (1)| 00:34:00 | 0 |00:00:00.01 | 0 | 9370K| 1189K| |
| 6 | TABLE ACCESS FULL | T_OTHER_TABLE | 0 | 87034 | 8329K| 345 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
|* 7 | VIEW PUSHED PREDICATE | | 0 | 1 | 220 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 8 | WINDOW SORT PUSHED RANK | | 0 | 1 | 98 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 9 | FILTER | | 0 | | | | | 0 |00:00:00.01 | 0 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE | 0 | 1 | 98 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX RANGE SCAN | I_T_HUGE_TABLE_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--执行连接顺序发生了变化,换一句话讲要推入成功,先要a,c连接,然后才与vm连接.也就是最后才能推入.注意这里a,c 使用MERGE JOIN CARTESIAN.
--这样就很容易明白问题在那里了:
1.在存在外连接的情况无法改变连接顺序.这也是使用left例子一会使用推入的关键.参考连接:http://blog.itpub.net/267265/viewspace-1991306/
2.要推入成功,必须是最后推入才行,也就是这个例子里面先连接a,c,在连接vm.
--这样增加条件a.object_id=c.object_id
WITH vm
AS (SELECT *
FROM (SELECT t.*
,ROW_NUMBER ()
OVER
(
PARTITION BY t.object_id
ORDER BY t.CREATED DESC
)
rn
FROM t_huge_table t)
WHERE rn = 1)
SELECT *
FROM t_small_table a
JOIN vm ON a.object_id = vm.object_id
JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu'
and a.object_id=c.object_id;
Plan hash value: 2525641501
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1840 (100)| | 0 |00:00:00.01 | 2 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 416 | 1840 (27)| 00:00:23 | 0 |00:00:00.01 | 2 | | | |
|* 2 | HASH JOIN | | 1 | 492 | 96432 | 364 (1)| 00:00:05 | 0 |00:00:00.01 | 2 | 813K| 813K| 174K (0)|
| 3 | TABLE ACCESS BY INDEX ROWID | T_SMALL_TABLE | 1 | 492 | 48216 | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | I_T_SMALL_TABLE_OWNER | 1 | 492 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS FULL | T_OTHER_TABLE | 0 | 87034 | 8329K| 347 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
|* 6 | VIEW PUSHED PREDICATE | | 0 | 1 | 220 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 7 | WINDOW SORT PUSHED RANK | | 0 | 1 | 98 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 8 | FILTER | | 0 | | | | | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE | 0 | 1 | 98 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 10 | INDEX RANGE SCAN | I_T_HUGE_TABLE_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--T_OTHER_TABLE 选择全表扫描主要是因为id=4 估计返回行数偏高492,导致选择全表扫描T_OTHER_TABLE.
WITH vm
AS (SELECT *
FROM (SELECT t.*
,ROW_NUMBER ()
OVER
(
PARTITION BY t.object_id
ORDER BY t.CREATED DESC
)
rn
FROM t_huge_table t)
WHERE rn = 1)
SELECT /*+index(c (object_id)) */ *
FROM t_small_table a
JOIN vm ON a.object_id = vm.object_id
JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu'
and a.object_id=c.object_id;
--或者修改如下,加入提示/*+ cardinality(a 5) */:
WITH vm
AS (SELECT *
FROM (SELECT t.*
,ROW_NUMBER ()
OVER
(
PARTITION BY t.object_id
ORDER BY t.CREATED DESC
)
rn
FROM t_huge_table t)
WHERE rn = 1)
SELECT /*+ cardinality(a 5) */ *
FROM t_small_table a
JOIN vm ON a.object_id = vm.object_id
JOIN t_other_table c ON vm.object_id = c.object_id
WHERE a.owner = 'kudfweu'
and a.object_id=c.object_id;
Plan hash value: 847857519
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 41 (100)| | 0 |00:00:00.01 | 2 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 416 | 41 (13)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 2 | NESTED LOOPS | | 1 | 5 | 980 | 26 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | T_SMALL_TABLE | 1 | 5 | 490 | 16 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | I_T_SMALL_TABLE_OWNER | 1 | 492 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | T_OTHER_TABLE | 0 | 1 | 98 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 6 | INDEX RANGE SCAN | I_T_OTHER_TABLE_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 7 | VIEW PUSHED PREDICATE | | 0 | 1 | 220 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 8 | WINDOW SORT PUSHED RANK | | 0 | 1 | 98 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 0 | 73728 | 73728 | |
|* 9 | FILTER | | 0 | | | | | 0 |00:00:00.01 | 0 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T_HUGE_TABLE | 0 | 1 | 98 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 11 | INDEX RANGE SCAN | I_T_HUGE_TABLE_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
总结:
1.在存在外连接的情况无法改变连接顺序.这也是使用left例子一会使用推入的关键.参考连接:http://blog.itpub.net/267265/viewspace-1991306/
2.要推入成功,必须是最后推入才行,也就是这个例子里面先连接a,c,在连接vm.
3.总觉的cbo还是不够智能.能力有限,也不知道如果中间就推入是否可能导致结果集合发生变化.