oracle sql 分区查询语句_Oracle SQL提示含义与示例 --- 模型化语句查询和分区提示...

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。

语法：MODEL_MIN_ANALYSIS

描述：用于模型化语句查询转换的提示。使得优化器对模型化语句的主查询做最少的查询转换分析。

分别对以下两条语句(有、无该提示)进行优化器过程跟踪：

HELLODBA.COM>alter session set events 'TRACE[RDBMS.SQL_Compiler.*]';

Session altered.

HELLODBA.COM>explain plan for

2SELECT /*+qb_name(m) no_merge(@inv) NO_MERGE(@"SEL$2")*/status, s

3FROM (select /*+qb_name(inv) no_merge(v)*/o.owner, o.status, o.object_name, o.created, t.tablespace_name from v_objects_sys o, t_tables t where o.owner=t.owner and o.object_name=t.table_name) q

4WHERE q.created < :A

5MODEL RETURN UPDATED ROWS

6PARTITION BY (status)

7DIMENSION BY (owner)

8MEASURES (object_name v, 1 s)

9RULES

10(s[any] = count(v) over (partition by status));

... ...

HELLODBA.COM>explain plan for

2SELECT /*+qb_name(m) MODEL_MIN_ANALYSIS no_merge(@inv) NO_MERGE(@"SEL$2")*/status, s

3FROM (select /*+qb_name(inv) no_merge(v)*/o.owner, o.status, o.object_name, o.created, t.tablespace_name from v_objects_sys o, t_tables t where o.owner=t.owner and o.object_name=t.table_name) q

4WHERE q.created < :A

5MODEL RETURN UPDATED ROWS

6PARTITION BY (status)

7DIMENSION BY (owner)

8MEASURES (object_name v, 1 s)

9RULES

10(s[any] = count(v) over (partition by status));

通过比较可以发现当使用该提示时，优化器为对模型化主语句做简单谓词推入的查询转换分析：

FPD: Considering simple filter push (pre rewrite) in query block M (#0)

FPD:Current where clause predicates??

try to generate transitive predicate from check constraints for query block M (#0)

finally:??

kkqfppRelFilter: Not pushing filter predicates in query block SEL$21876068 (#0) because no predicate to push

FPD: Considering simple filter push (pre rewrite) in query block SEL$21876068 (#0)

FPD:Current where clause predicates "T_OBJECTS"."OWNER"="T"."OWNER" AND "T_OBJECTS"."OBJECT_NAME"="T"."TABLE_NAME" AND "T_OBJECTS"."OWNER"='SYS' AND "T_OBJECTS"."CREATED"<:b1 and>

语法：MODEL_NO_ANALYSIS

描述：用于模型化语句查询转换的提示。使得优化器不对模型化语句的主查询做查询转换分析。

可以对比有无该提示的优化器跟踪记录观察其影响。

语法：MODEL_PUSH_REF

描述：未知。可能是用于模型化语句查询转换的提示，指示优化器将主模型中的谓词条件推入引用模型当中。

语法：NO_MODEL_PUSH_REF

描述：未知。可能是用于模型化语句查询转换的提示，禁止优化器将主模型中的谓词条件推入引用模型当中。

语法：MODEL_COMPILE_SUBQUERY

描述：未知。可能是用于模型化语句查询转换的提示。

语法：MODEL_DONTVERIFY_UNIQUENESS

描述：未知。可能是用于模型化语句查询转换的提示。

语法：MODEL_DYNAMIC_SUBQUERY

描述：未知。可能是用于模型化语句查询转换的提示。

语法：X_DYN_PRUNE

描述：指示优化器通过运行时刻子查询的结果对分区进行动态裁剪。

HELLODBA.COM>alter session set tracefile_identifier = 'hash_X_DYN_PRUNE(10128)';

Session altered.

HELLODBA.COM>alter session set events '10128 trace name context forever, level 31';

Session altered.

HELLODBA.COM>select /*+use_hash(tr t2) X_DYN_PRUNE*/tr.* from t_objects_range tr, t_tables t2 where tr.owner=t2.owner and tr.object_name=t2.table_name and t2.tablespace_name='USERS';

… …

HELLODBA.COM>exec sql_explain('select /*+use_hash(tr t2) X_DYN_PRUNE*/tr.* from t_objects_range tr, t_tables t2 where tr.owner=t2.owner and tr.object_name=t2.table_name and t2.tablespace_name=''USERS''', 'BASIC OUTLINE');

---------------------------------------------------------

| Id| Operation| Name|

---------------------------------------------------------

|0 | SELECT STATEMENT||

|1 |HASH JOIN||

|2 |PART JOIN FILTER CREATE| :BF0000|

|3 |TABLE ACCESS BY INDEX ROWID| T_TABLES|

|4 |INDEX RANGE SCAN| T_TABLES_IDX3|

|5 |PARTITION RANGE JOIN-FILTER ||

|6 |TABLE ACCESS FULL| T_OBJECTS_RANGE |

---------------------------------------------------------

上述语句的10128跟踪信息中，可以看到以下记录：

kkpapDAExtSQuerySLvl strings sql1 SELECT distinct TBL$OR$IDX$PART$NUM("T_OBJECTS_RANGE", 0,sql2 "OWNER", "OBJECT_NAME") FROM (SELECT "A1"."OWNER" "OWNER", "A1"."TABLE_NAME" "OBJECT_NAME" FROM "T_TABLES" "A1" WHERE "A1"."TABLESPACE_NAME"='USERS') ORDER BY 1

语法：SUBQUERY_PRUNING([] PARTITION)

描述：指示优化器使用子查询对分区表进行分区裁剪。SUBQUERY_PRUNING/NO_SUBQUERY_PRUNING是用于控制执行计划中否采用子查询裁剪的概要数据，而X_DYN_PRUNE则用于执行时是否进行子查询裁剪。

HELLODBA.COM>exec sql_explain('select /*+ use_merge(tr t2) SUBQUERY_PRUNING(tr PARTITION)*/tr.* from t_objects_range tr, t_tables t2 where tr.owner=t2.owner and tr.object_name=t2.table_name and t2.tablespace_name=''SYSTEM''', 'BASIC');

-------------------------------------------------------

| Id| Operation| Name|

-------------------------------------------------------

|0 | SELECT STATEMENT||

|1 |MERGE JOIN||

|2 |SORT JOIN||

|3 |VIEW| index$_join$_002 |

|4 |HASH JOIN||

|5 |INDEX RANGE SCAN| T_TABLES_IDX3|

|6 |INDEX FAST FULL SCAN| T_TABLES_PK|

|7 |SORT JOIN||

|8 |PARTITION RANGE SUBQUERY||

|9 |TABLE ACCESS FULL| T_OBJECTS_RANGE |

-------------------------------------------------------

从10128事件跟踪信息中可以看到以下记录：

SQL text = text = SELECT distinct TBL$OR$IDX$PART$NUM("T_OBJECTS_RANGE", 0,"OWNER", "OBJECT_NAME") FROM (SELECT "T2"."OWNER" "OWNER", "T2"."TABLE_NAME" "OBJECT_NAME" FROM "T_TABLES" "T2" WHERE "T2"."TABLESPACE_NAME"='SYSTEM') ORDER BY 1}

语法：NO_SUBQUERY_PRUNING([] )

描述：禁止优化器使用子查询对分区表进行分区裁剪

HELLODBA.COM>exec sql_explain('select /*+NO_SUBQUERY_PRUNING(O PARTITION) LEADING(u)*/o.* from t_objects_range o, t_tables t, t_users u where o.owner=t.owner and o.object_name=t.table_name and o.owner=u.username and t.tablespace_name=''USERS'' and u.user_id<10', 'BASIC OUTLINE');

----------------------------------------------------------

| Id| Operation| Name|

----------------------------------------------------------

|0 | SELECT STATEMENT||

|1 |NESTED LOOPS||

|2 |NESTED LOOPS||

|3 |HASH JOIN||

|4 |TABLE ACCESS BY INDEX ROWID| T_USERS|

|5 |INDEX RANGE SCAN| T_USERS_PK|

|6 |PARTITION RANGE ALL||

|7 |TABLE ACCESS FULL| T_OBJECTS_RANGE |

|8 |INDEX UNIQUE SCAN| T_TABLES_PK|

|9 |TABLE ACCESS BY INDEX ROWID| T_TABLES|

----------------------------------------------------------