Category Archives: Hint

NO_Eliminate_Join and FK Constraint Novalidate

RY IS *******
SELECT “C”.”CUST_FIRST_NAME” “CUST_FIRST_NAME” FROM “BLUE”.”C_CUSTOMERS” “C”,”BLUE”.”P_COUNTRIES” “P” WHERE “C”.”COUNTRY_ID”=”P”.”COUNTRY_ID”
JE: cfro: C_CUSTOMERS objn:136533 col#:3 dfro:P_COUNTRIES dcol#:1
JE: cfro: C_CUSTOMERS objn:136533 col#:3 dfro:P_COUNTRIES dcol#:1
Query block (0x4abb39d30) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT “C”.”CUST_FIRST_NAME” “CUST_FIRST_NAME” FROM “BLUE”.”C_CUSTOMERS” “C”,”BLUE”.”P_COUNTRIES” “P” WHERE “C”.”COUNTRY_ID”=”P”.”COUNTRY_ID”
JE: eliminate table: P_COUNTRIES (P)
Registered qb: SEL$A43D1678 0xabb39d30 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; “P”@”SEL$1″)
———————
QUERY BLOCK SIGNATURE
———————
signature (): qb_name=SEL$A43D1678 nbfros=1 flg=0
fro(0): flg=0 objn=136535 hint_alias=”C”@”SEL$1”

SQL:******* UNPARSED QUERY IS *******
SELECT “C”.”CUST_FIRST_NAME” “CUST_FIRST_NAME” FROM “BLUE”.”C_CUSTOMERS” “C”
Query block SEL$A43D1678 (#0) simplified

使用no_eliminate_join阻止join 消除。

SQL> select /*+ no_eliminate_join(p) */
  2   c.cust_first_name
  3    from c_customers c, p_countries p
  4   where c.country_id = p.country_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3595640479

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |              |     1 |    38 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| C_CUSTOMERS  |     1 |    25 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_COUNTRIES |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C"."COUNTRY_ID"="P"."COUNTRY_ID")

10053内容如下

*************************
Join Elimination (JE)    
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ NO_ELIMINATE_JOIN ("P") */ "C"."CUST_FIRST_NAME" "CUST_FIRST_NAME" FROM "BLUE"."C_CUSTOMERS" "C","BLUE"."P_COUNTRIES" "P" WHERE "C"."COUNTRY_ID"="P"."COUNTRY_ID"
JE:   cfro: P_COUNTRIES objn:136535 col#:1 dfro:C_CUSTOMERS dcol#:3
SQL:******* UNPARSED QUERY IS *******
SELECT /*+ NO_ELIMINATE_JOIN ("P") */ "C"."CUST_FIRST_NAME" "CUST_FIRST_NAME" FROM "BLUE"."C_CUSTOMERS" "C","BLUE"."P_COUNTRIES" "P" WHERE "C"."COUNTRY_ID"="P"."COUNTRY_ID"
Query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"C"."COUNTRY_ID"="P"."COUNTRY_ID"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "C"."COUNTRY_ID"="P"."COUNTRY_ID"

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ NO_ELIMINATE_JOIN ("P") */ "C"."CUST_FIRST_NAME" "CUST_FIRST_NAME" FROM "BLUE"."C_CUSTOMERS" "C","BLUE"."P_COUNTRIES" "P" WHERE "C"."COUNTRY_ID"="P"."COUNTRY_ID"
kkoqbc: optimizing query block SEL$1 (#0)


*********************************
Number of join permutations tried: 2
*********************************
Consider using bloom filter between C[C_CUSTOMERS] and P[P_COUNTRIES] with ??
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000)  rejected because not a hash join
Enumerating distribution method (advanced)
--- Distribution method for 
join between C[C_CUSTOMERS](serial) and P[P_COUNTRIES](serial); jm = 12; right side access path = IndexUnique
---- NLJ default -> BROADCAST-LEFT

3 - access("C"."COUNTRY_ID"="P"."COUNTRY_ID")
 
Content of other_xml column
===========================
  db_version     : 11.2.0.4
  parse_schema   : BLUE
  plan_hash      : 3595640479
  plan_hash_2    : 1993472567
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "C"@"SEL$1")
      INDEX(@"SEL$1" "P"@"SEL$1" ("P_COUNTRIES"."COUNTRY_ID"))
      LEADING(@"SEL$1" "C"@"SEL$1" "P"@"SEL$1")
      USE_NL(@"SEL$1" "P"@"SEL$1")
    END_OUTLINE_DATA
  */

继续阅读

发表在 CBO, Hint | 留下评论

cardinality hint

cardinality hint可以通过强制的方式告诉Oracle优化器会返回多 … 继续阅读

发表在 Hint, Uncategorized | 留下评论

no_swap_join_inputs and swap_join_inputs hint

当oracle优化器选择hash join作为执行计划join时,如果我们希望o … 继续阅读

发表在 Hint | 留下评论

no_use_hash hint

The NO_USE_HASH hint instructs the optim … 继续阅读

发表在 Hint | 留下评论

no_push_pred hint

The NO_PUSH_PRED hint instructs the opti … 继续阅读

发表在 Hint | 留下评论

push_pred

The PUSH_PRED hint instructs the optimiz … 继续阅读

发表在 Hint, Uncategorized | 留下评论

push_subq hint

The PUSH_SUBQ hint instructs the optimiz … 继续阅读

发表在 Hint, Uncategorized | 留下评论