5、外键“想当然”
外键Foreign Key是维持参照完整性的重要手段。对CBO而言，Foreign Key意味着取值规则上的差异，也会影响落实到执行计划中。
首先创建实验环境。
SQL> create table t_master (id number, mas_name varchar2(100));
Table created
SQL> create table t_child (cid number, id number, chi_name varchar2(100));
Table created
SQL> alter table t_master add constraint pk_t_master primary key (id);
Table altered
SQL> alter table t_child add constraint pk_t_child primary key (cid);
Table altered
SQL> alter table t_child add constraint fk_t_child_master foreign key (id) references t_master(id);
Table altered
--收集统计量
SQL> exec dbms_stats.gather_table_stats(user, 'T_MASTER', cascade => true);
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user, 'T_CHILD', cascade => true);
PL/SQL procedure successfully completed
我们研究一个SQL语句，讨论查找不存在主表中的子表记录。
SQL> explain plan for select * from t_child where id not in (select id from t_master);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1412281931
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 3 (34)| 00:00:01
|* 1 | HASH JOIN ANTI SNA| | 1 | 91 | 3 (34)| 00:00:01
| 2 | TABLE ACCESS FULL| T_CHILD | 1 | 78 | 2 (0)| 00:00:01
| 3 | INDEX FULL SCAN | PK_T_MASTER | 1 | 13 | 0 (0)| 00:00:01
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"="ID")
15 rows selected
Not in的标准过程是使用反连接动作，上面的执行计划也证明了这点。但是，这个执行计划没有考虑外键因素。在外键约束中，外键列上是不可能出现不是空值的非主键值。Not in最大的警惕点在于是否有空。
我们调节外键列的属性约束，取得新的执行计划。
SQL> alter table t_child modify id not null;
Table altered
SQL> explain plan for select * from t_child where id not in (select id from t_master);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2639974602
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 78 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_CHILD | 1 | 78 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
14 rows selected
成本值是0！执行计划中的确有对子表的FTS动作，但是上面的那个filter，引用了一个条件：null is not null——永假式。
SQL> select 1 from dual where NULL IS NOT NULL;
1
----------
Oracle在承认子表id非空之后，直接判定这个SQL是不会有任何结果的。索性执行给一个永假式，返回0行记录。
这种取巧路径在RBO时代，也是不存在的。
SQL> explain plan for select /*+rule*/* from t_child where id not in (select id from t_master);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2659151261
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| T_CHILD |
|* 3 | INDEX UNIQUE SCAN| PK_T_MASTER |
------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "T_MASTER" "T_MASTER" WHERE
"ID"=:B1))
3 - access("ID"=:B1)
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- rule based optimizer used (consider using cbo)
21 rows selected
6、结论
执行计划是SQL语句执行效率的命脉。如何生成更快速的执行计划，不仅是Oracle，更是所有优化器追求的方向目标。在CBO时代，Oracle放弃了RBO时候简单15个等级的策略，应用成本Cost度量执行计划策略，统计量+内核公式构成了优化器比较基础。
除此之外，我们说数据库对象的描述，特别是约束起到更加重要的作用。本篇中，我们介绍了几个关键场景下，约束对于执行计划的作用。从直观上看，约束描述可以帮助CBO找到各种“取巧”路径。这实际上就反映了约束对于数据库的描述作用，生成高效执行计划。
对于开发设计人员，这个结论很重要。实际工作中，不少开发人员忽视约束的作用。不愿意进行字段非空讨论，不进行适度外键设计，这些都是需要我们借鉴的方面。