1.定义

PM - predicate move-around

A new type of optimization, called predicate move-around, is introduced. It is shown how this optimization considerably improves the efficiency of evaluating SQL queries that have query graphs with a large number of query blocks (which is a typical situation when queries are defined in terms of multiple views and subqueries). Predicate move-around works by moving predicates across query blocks (in the query graph) that cannot be merged into one block. Predicate move-around is a generalization of and has many advantages over the traditional predicate pushdown. One key advantage arises from the fact that predicate move-around precedes pushdown by pulling predicates up the query graph. As a result, predicates that appear in the query in one part of the graph can be moved around the graph and applied also in other parts of graph.

Moreover, predicate move-around optimization can move a wider class of predicates in a wider class of queries as compared to the standard predicate-pushdown techniques. In addition to the usual comparison and arithmetic predicates, other predicates that can be moved around are the EXISTS and NOT EXISTS clauses, the EXCEPT clause, and functional dependencies. The proposed optimization can also move predicates through aggregation. Moreover, the method can also infer new predicates when existing predicates are moved through aggregation or when certain functional dependencies are known to hold. Finally, the predicate move-around algorithm is easy to implement on top of existing query optimizers.

简单来说就是多个视图的查询中，将某一个视图的谓词条件Pull up出来并且push down到另一个视图中。

2.参数

KSPPINM KSPPSTVL KSPPDESC
------------------------------ ---------- ------------------------------------------------------------
_pred_move_around TRUE enables predicate move-around

3.测试

sql文本：

select /*+ qb_name(main) no_merge(v1) no_merge(v2)*/* from
(select /*+ qb_name(sb)*/* from lxy where object_id<100) v1,
(select /*+ qb_name(nb)*/* from lxy_1 where object_type='TABLE') v2
where v1.object_id=v2.object_id;

这里需要禁用视图合并，不然该sql会优先视图合并

执行计划：

Plan hash value: 4072594799
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 1242 | 8 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 1242 | 8 (0)| 00:00:01 |
| 2 | VIEW | | 3 | 621 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| LXY_1 | 3 | 267 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I2 | 95 | | 2 (0)| 00:00:01 |
| 5 | VIEW | | 95 | 19665 | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| LXY | 95 | 8455 | 4 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I1 | 95 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MAIN
2 - NB / V2@MAIN
3 - NB / LXY_1@NB
4 - NB / LXY_1@NB
5 - SB / V1@MAIN
6 - SB / LXY@SB
7 - SB / LXY@SB
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"NB" "LXY_1"@"NB" ("LXY_1"."OBJECT_ID"))
INDEX_RS_ASC(@"SB" "LXY"@"SB" ("LXY"."OBJECT_ID"))
USE_HASH(@"MAIN" "V1"@"MAIN")
LEADING(@"MAIN" "V2"@"MAIN" "V1"@"MAIN")
NO_ACCESS(@"MAIN" "V1"@"MAIN")
NO_ACCESS(@"MAIN" "V2"@"MAIN")
OUTLINE(@"MAIN")
OUTLINE(@"NB")
OUTLINE(@"SB")
OUTLINE_LEAF(@"MAIN")
OUTLINE_LEAF(@"NB")
OUTLINE_LEAF(@"SB")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V1"."OBJECT_ID"="V2"."OBJECT_ID")
3 - filter("OBJECT_TYPE"='TABLE')
4 - access("LXY_1"."OBJECT_ID"<100)
7 - access("OBJECT_ID"<100)

CBO将查询块sb中的谓词提取了出来，并且推入了查询块NB中。同时该谓词在原查询块中也存在。从10053可以得到验证。

10053：

PM: Considering predicate move-around in query block MAIN (#0)
**************************
Predicate Move-Around (PM)
**************************
PM: Passed validity checks.
PM: Pulled up predicate "V1"."OBJECT_ID"<100 from query block SB (#0) to query block MAIN (#0) --从query block SB提取谓词"V1"."OBJECT_ID"<100到query block MAIN
PM: Pulled up predicate "V2"."OBJECT_TYPE"='TABLE' from query block NB (#0) to query block MAIN (#0) --从query block NB提取"V2"."OBJECT_TYPE"='TABLE'到qb MAIN
PM: Added transitive pred "V2"."OBJECT_ID"<100 in query block MAIN (#0) --由于"V1"."OBJECT_ID"="V2"."OBJECT_ID" 所以transitive pred "V2"."OBJECT_ID"<100
PM: Pushed down predicate "LXY_1"."OBJECT_ID"<100 from query block MAIN (#0) to query block NB (#0) --"LXY_1"."OBJECT_ID"<100从query block MAIN推入query block NB
PM: PM bypassed: checking. query block MAIN (#0) unchanged

最后转换后的语句为：

SELECT /*+ QB_NAME ("MAIN") NO_MERGE ("V2") NO_MERGE ("V1") */ *
FROM (SELECT /*+ QB_NAME ("SB") */ * FROM "SYS"."LXY" "LXY" WHERE "LXY"."OBJECT_ID" < 100) "V1",
(SELECT /*+ QB_NAME ("NB") */ * FROM "SYS"."LXY_1" "LXY_1" WHERE "LXY_1"."OBJECT_TYPE" = 'TABLE' AND "LXY_1"."OBJECT_ID" < 100) "V2"
WHERE "V1"."OBJECT_ID" = "V2"."OBJECT_ID"

ITPub博客

Predicate Move-around