HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle SQL Hints --- Model Query And Partition Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

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

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

 

Model Query Hints

MODEL_MIN_ANALYSIS

Usage: MODEL_MIN_ANALYSIS

Description: Instructs the optimizer to do minimum transformation analysis on the main query when performingquery 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        |

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

Top

Copyright ©2005, HelloDBA.Com All reseverd.

Declaration
by fuyuncat