Oracle SQL Hints --- Hierarchy Query And XML Query Hints

Date: 2013-01-08 05:12:05

Share to

Hierarchy Query Hints

CONNECT_BY_CB_WHR_ONLY

Usage: CONNECT_BY_CB_WHR_ONLY([<@Block>])

Description: Unknown. It might instruct the optimizer to consider the WHERE clause only when performing CONNECT BY operation. There is an optimizer parameter, _optimizer_connect_by_cb_whr_only, to control this feature.

NO_CONNECT_BY_CB_WHR_ONLY

Usage: NO_CONNECT_BY_CB_WHR_ONLY([<@Block>])

Description: Unknown. It might prevent the optimizer to consider the WHERE clause only when performing CONNECT BY operation.

CONNECT_BY_COMBINE_SW

Usage: CONNECT_BY_COMBINE_SW([<@Block>])

Description: Instructs the optimizer to consider the predication in START WITH when performing CONNECT BY operation.

HELLODBA.COM>exec sql_explain('select /*+NO_CONNECT_BY_FILTERING(@SEL$1) CONNECT_BY_COMBINE_SW(@SEL$1)*/owner, table_name, level from t_constraints connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A','BASIC OUTLINE PREDICATE');

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

| Id | Operation                               | Name          |

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

| 0 | SELECT STATEMENT                        |               |

|* 1 | CONNECT BY NO FILTERING WITH START-WITH|               |

|   2 |   TABLE ACCESS FULL                     | T_CONSTRAINTS |

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

Predicate Information (identified by operation id):

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

   1 - access("OWNER"=PRIOR "R_OWNER" AND "CONSTRAINT_NAME"=PRIOR

              "R_CONSTRAINT_NAME")

       filter("OWNER"=:A)

NO_CONNECT_BY_COMBINE_SW

Usage: NO_CONNECT_BY_COMBINE_SW([<@Block>])

Description: Prevents the optimizer to consider the predication in START WITH when performing CONNECT BY operation.

HELLODBA.COM>exec sql_explain('select /*+NO_CONNECT_BY_FILTERING(@"SEL$1") NO_CONNECT_BY_COMBINE_SW(@"SEL$1")*/owner, table_name, level from t_constraints connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A','BASIC OUTLINE PREDICATE');

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

| Id | Operation                    | Name          |

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

|   0 | SELECT STATEMENT             |               |

|* 1 | CONNECT BY WITHOUT FILTERING|               |

|* 2 |   TABLE ACCESS FULL          | T_CONSTRAINTS |

|   3 |   TABLE ACCESS FULL          | T_CONSTRAINTS |

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

Predicate Information (identified by operation id):

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

   1 - access("OWNER"=PRIOR "R_OWNER" AND "CONSTRAINT_NAME"=PRIOR

              "R_CONSTRAINT_NAME")

   2 - filter("OWNER"=:A)

CONNECT_BY_COST_BASED

Usage: CONNECT_BY_COST_BASED([<@Block>])

Description: Instructs the optimizer to transform CONNECT BY based on cost.

Demo (10.2.0.4):

HELLODBA.COM>exec sql_explain('select /*+QB_NAME(M) CONNECT_BY_COST_BASED(@M)*/owner, table_name, level from t_constraints where constraint_type=:B connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A and level > 2','BASIC OUTLINE PREDICATE');

… …

Outline Data

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

/*+

      BEGIN_OUTLINE_DATA

      … …

      OUTLINE(@"M")

      CONNECT_BY_COST_BASED(@"M")

      OUTLINE(@"SEL$C20E7289")

      CONNECT_BY_FILTERING(@"SEL$C20E7289")

      … …

      IGNORE_OPTIM_EMBEDDED_HINTS

      END_OUTLINE_DATA

*/

NO_CONNECT_BY_COST_BASED

Usage: NO_CONNECT_BY_COST_BASED([<@Block>])

Description: Instructs the optimizer to transform CONNECT BY based on cost.

Demo (10.2.0.4):

HELLODBA.COM>exec sql_explain('select /*+QB_NAME(M) NO_CONNECT_BY_COST_BASED(@M)*/owner, table_name, level from t_constraints where constraint_type=:B connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A and level > 2','BASIC OUTLINE PREDICATE');

BEGIN sql_explain('select /*+QB_NAME(M) NO_CONNECT_BY_COST_BASED(@M)*/owner, table_name, level from t_constraints where constraint_type=:B connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A and level > 2','BASIC OUTLINE PREDICATE'); END;

*

ERROR at line 1:

ORA-01788: CONNECT BY clause required in this query block

ORA-06512: at "SYS.SQL_EXPLAIN", line 25

ORA-06512: at line 1

CONNECT_BY_ELIM_DUPS

Usage: CONNECT_BY_ELIM_DUPS([<@Block>])

Description: Instructs the SQL executor to eliminate duplicated data when perform CONNECT BY operation.

HELLODBA.COM>exec sql_explain('SELECT /*+qb_name(BV) CONNECT_BY_ELIM_DUPS(@"BV")*/DISTINCT LEVEL FROM DUAL CONNECT BY LEVEL <= 10', 'TYPICAL OUTLINE');

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

| Id | Operation                              | Name | Rows | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                       |      |     1 |     3 (34)| 00:00:01 |

|   1 | HASH UNIQUE                           |      |     1 |     3 (34)| 00:00:01 |

|* 2 |   CONNECT BY WITHOUT FILTERING (UNIQUE)|      |       |            |          |

|   3 |    FAST DUAL                           |      |     1 |     2   (0)| 00:00:01 |

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

NO_CONNECT_BY_ELIM_DUPS

Usage: NO_CONNECT_BY_ELIM_DUPS([<@Block>])

Description: Prevents the SQL executor to eliminate duplicated data when perform CONNECT BY operation.

HELLODBA.COM>exec sql_explain('SELECT /*+qb_name(BV) NO_CONNECT_BY_ELIM_DUPS(@"BV")*/DISTINCT LEVEL FROM DUAL CONNECT BY LEVEL <= 10', 'TYPICAL OUTLINE');

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

| Id | Operation                     | Name | Rows | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT              |      |     1 |     3 (34)| 00:00:01 |

|   1 | HASH UNIQUE                  |      |     1 |     3 (34)| 00:00:01 |

|* 2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |

|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |

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

CONNECT_BY_FILTERING

Usage: CONNECT_BY_FILTERING([<@Block>])

Description: Instructs the SQL executor to filter data when perform CONNECT BY operation.

HELLODBA.COM>exec sql_explain('select /*+CONNECT_BY_FILTERING(@"SEL$1")*/owner, table_name, level from t_constraints c start with constraint_name in (select index_name from t_indexes t where t.owner = c.owner ) connect by prior r_owner=owner and prior r_constraint_name = constraint_name','BASIC');

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

| Id | Operation                 | Name          |

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

|   0 | SELECT STATEMENT          |               |

|   1 | CONNECT BY WITH FILTERING|               |

|   2 |   NESTED LOOPS            |               |

|   3 |    TABLE ACCESS FULL      | T_CONSTRAINTS |

|   4 |    INDEX UNIQUE SCAN      | T_INDEXES_PK |

|   5 |   HASH JOIN               |               |

|   6 |    CONNECT BY PUMP        |               |

|   7 |    TABLE ACCESS FULL      | T_CONSTRAINTS |

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

NO_CONNECT_BY_FILTERING

Usage: NO_CONNECT_BY_FILTERING([<@Block>])

Description: Prevents the SQL executor to filter data when perform CONNECT BY operation.

HELLODBA.COM>exec sql_explain('select /*+NO_CONNECT_BY_FILTERING(@"SEL$1")*/owner, table_name, level from t_constraints connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A','BASIC');

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

| Id | Operation                               | Name          |

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

|   0 | SELECT STATEMENT                        |               |

|   1 | CONNECT BY NO FILTERING WITH START-WITH|               |

|   2 |   TABLE ACCESS FULL                     | T_CONSTRAINTS |

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

XML查询提示

COST_XML_QUERY_REWRITE

Usage: COST_XML_QUERY_REWRITE

escription: Instructs the optimizer to perform XML query rewrite based on cost. By default, the optimizer will perform XML query rewrite based on rules. We can the reason why cost based rewrite failed from the trace file of 19027 event.

HELLODBA.COM>desc xml_test

Name                                          Null?    Type

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

TABLE of SYS.XMLTYPE(XMLSchema "http://www.HelloDBA.com/xml/schema.xsd" Element "test-xml") STORAGE Object-relational TYPE "test-xml748_T"

HELLODBA.COM>exec sql_explain('SELECT /*+*/EXTRACT(VALUE(x), ''/test-xml/id'') FROM xml_test x WHERE EXTRACTVALUE(value(x), ''/test-xml/name'') = ''aaa''', 'TYPICAL OUTLINE');

Plan hash value: 3532887779

… …

Outline Data

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

/*+