NO_Eliminate_Join and FK Constraint Novalidate

Oracle从10gR2优化器引入了一个table elimination,也叫join elimination,关于这一转换的说明请参考optimizer团队的why_are_some_of_the_tables_in_my_query_missing_from_the_plan这篇文章,而且在11g中又有了新的增强,Antognini的join-elimination对2个版本进行了测试和描述。
本文中主要关注no_eliminate_join和约束的状态对join elimination的影响,测试环境为11.2.0.4
测试脚本

drop table c_customers purge;
drop table p_countries purge;
create table p_countries
(
country_id NUMBER not null,
country_name VARCHAR2(40) not null
);
alter table p_countries add constraint pk_countries primary key(country_id);

create table c_customers
(
cust_id number not null primary key,
cust_first_name varchar2(20) not null,
country_id NUMBER not null
);
alter table c_customers add constraint fk_customers_contry foreign key(country_id)
references p_countries(country_id);

begin dbms_stats.gather_table_stats(
user,
'p_countries',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

begin dbms_stats.gather_table_stats(
user,
'c_customers',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

ALTER SESSION SET events '10053 trace name context forever';

select c.cust_first_name from c_customers c,p_countries p
where c.country_id = p.country_id;

select /*+ no_eliminate_join(p) */
c.cust_first_name
from c_customers c, p_countries p
where c.country_id = p.country_id;

alter table c_customers disable constraint fk_customers_contry;
select c.cust_first_name from c_customers c,p_countries p
where c.country_id = p.country_id;

insert into C_CUSTOMERS values(60000,'china',60000);
commit;

alter table c_customers enable novalidate constraint fk_customers_contry;
select c.cust_first_name from c_customers c,p_countries p
where c.country_id = p.country_id;

ALTER SESSION SET events '10053 trace name context off';

DEMO
默认进行了join elimination

SQL> select c.cust_first_name from c_customers c,p_countries p
  2  where c.country_id = p.country_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 2652026743

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    12 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| C_CUSTOMERS |     1 |    12 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------
10053内容如下
 
Content of other_xml column
===========================
  db_version     : 11.2.0.4
  parse_schema   : BLUE
  plan_hash      : 2652026743
  plan_hash_2    : 3096753008
  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$A43D1678")
      ELIMINATE_JOIN(@"SEL$1" "P"@"SEL$1")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$A43D1678" "C"@"SEL$1")
    END_OUTLINE_DATA
  */

JE:   Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)    
*************************
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:   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
  */

禁掉约束以及约束novalidate的时候都无法进行join消除。

SQL> alter table c_customers disable constraint fk_customers_contry;

Table altered.

SQL> select c.cust_first_name from c_customers c,p_countries p
  2  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")


SQL> insert into C_CUSTOMERS values(60000,'china',60000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table c_customers enable novalidate constraint  fk_customers_contry;

Table altered.

SQL> select c.cust_first_name from c_customers c,p_countries p
  2  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")

当主外键的约束被novalidate进行enable之后,Oracle优化器无法进行join elimination,所以尽量保持主外键之间是validate状态。
_optimizer_join_elimination_enabled是控制是否可以进行join消除的一个隐含参数,随着join elimination的引入也引起了很多的bug,当遇到bug或者不像使用这个特性的时候可以考虑关闭掉。

此条目发表在CBO, Hint分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s