Oracle SQL Hints --- Model Query And Partition Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 05:18:21

Share to 

Model Query Hints MODEL_MIN_ANALYSIS Usage: MODEL_MIN_ANALYSIS Description: Instructs the optimizer to do minimum transformation analysis on the main query when performing用query transformation on Model Query.        Trace below two queries with/without this hint: HELLODBA.COM>alter session set events 'TRACE[RDBMS.SQL_Compiler.*]';   Session altered.   HELLODBA.COM>explain plan for   2  SELECT /*+qb_name(m) no_merge(@inv) NO_MERGE(@"SEL$2")*/status, s   3    FROM (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   4   WHERE q.created < :A   5    MODEL RETURN UPDATED ROWS   6      PARTITION BY (status)   7      DIMENSION BY (owner)   8      MEASURES (object_name v, 1 s)   9      RULES  10      (s[any] = count(v) over (partition by status));   ... ...   HELLODBA.COM>explain plan for   2  SELECT /*+qb_name(m) MODEL_MIN_ANALYSIS no_merge(@inv) NO_MERGE(@"SEL$2")*/status, s   3    FROM (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   4   WHERE q.created < :A   5    MODEL RETURN UPDATED ROWS   6      PARTITION BY (status)   7      DIMENSION BY (owner)   8      MEASURES (object_name v, 1 s)   9      RULES  10      (s[any] = count(v) over (partition by status));   Comparing the trace content, we can find the optimizer performed simple filter push analysis: 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 "T"."OWNER"='SYS' MODEL_NO_ANALYSIS Usage: MODEL_NO_ANALYSIS Description: Prevents the optimizer to do transformation analysis on the main query when performing. We can compare the optimizer tracing records to confirm the effect of this hint. MODEL_PUSH_REF Usage: MODEL_PUSH_REF               Description: Unknown. It might instruct the optimizer to push the predication in the main model to reference model. NO_MODEL_PUSH_REF Usage: NO_MODEL_PUSH_REF Description: Unknown. It might prevent the optimizer to push the predication in the main model to reference model. MODEL_COMPILE_SUBQUERY Usage: MODEL_COMPILE_SUBQUERY Description: Unknown. It might be used for model query transformation. MODEL_DONTVERIFY_UNIQUENESS Usage: MODEL_DONTVERIFY_UNIQUENESS  Description: Unknown. It might be used for model query transformation. MODEL_DYNAMIC_SUBQUERY Usage: MODEL_DYNAMIC_SUBQUERY Description: Unknown. It might be used for model query transformation. Partitioning Hints X_DYN_PRUNE Usage: X_DYN_PRUNE Description: Instructs the SQL executor to using the result of sub query to prune the partitions dynamically. 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 | ---------------------------------------------------------   From the trace records of 10128 event, we got below entries: 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 Usage: SUBQUERY_PRUNING([<@Block>] <Table> PARTITION) Description: Instructs the optimizer to using sub query to prune the partitions dynamically. 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  | -------------------------------------------------------   From the trace records of 10128 event, we got below entries: 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 Usage: NO_SUBQUERY_PRUNING([<@Block>] <Table>) Description: Prevents the optimizer to using sub query to prune the partitions dynamically. 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        | ----------------------------------------------------------