Oracle优化器经历RBO到CBO的演进过程，其能力已经远远超过“条条大路通罗马”的程度，而进入到智能化程度。那么，我们怎么样规划应用架构、设计数据表和写好SQL，就是我们一直关注的问题。在笔者之前的文章中，一直强调一个概念就是“描述”。我们不仅仅要描述好我们的SQL，更要描述好我们的数据表，以期利于CBO的工作。

约束是数据对象的一个重要特征，常见的约束有非空、主外键和唯一性约束。约束本质上就是业务逻辑在数据库层面的一种体现。对Oracle数据库而言，约束还意味着“数据内部规律的承诺”。在SQL Optimizer工作的时候，约束会让优化器能够获取到更好的执行计划。

笔者过去常常用“not null下count(*)”的案例。本篇介绍主键和外键约束对执行计划影响。

1、环境介绍

我们选择Oracle 11g进行试验。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

2、问题提出

在scott用户下面，有常见的三个数据表bonse、dept和emp。我们希望进行直接连接结果。

SQL> explain plan for select count(*) from scott.bonus a, scott.dept b, scott.emp c where a.ename=c.ename and b.deptno=c.deptno;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2843340944

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

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

| 0 | SELECT STATEMENT | | 1 | 16 | 6 (17)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 16 | | |

|* 2 | HASH JOIN | | 1 | 16 | 6 (17)| 00:00:01 |

| 3 | TABLE ACCESS FULL| BONUS | 1 | 7 | 2 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("A"."ENAME"="C"."ENAME")

4 - filter("C"."DEPTNO" IS NOT NULL)

17 rows selected

诡异的情况出现了。在SQL里面，我们明明写了三个数据表连接。但是执行计划里面只出现了两个数据表连接动作。第三个连接表dept就变成了一个filter谓词。

更进一步，如果我们就当dept和emp连接的时候，也看到了诡异的情况。

SQL> explain plan for select count(*) from scott.dept b, scott.emp c where b.deptno=c.deptno;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2083865914

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

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

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

| 1 | SORT AGGREGATE | | 1 | 3 | | |

|* 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter("C"."DEPTNO" IS NOT NULL)

14 rows selected

3、使用10046分析过程

两个表连接，也被踢掉dept了。那么是什么原因呢？我们使用10053进行分析，看看Oracle的决策过程。

在开始过程，Oracle是承认里面包括三个查询块。

Registered qb: SEL$1 0x9b35d4 (PARSER)

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

QUERY BLOCK SIGNATURE

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

signature (): qb_name=SEL$1 nbfros=3 flg=0

fro(0): flg=4 bjn=73183 hint_alias="A"@"SEL$1"

fro(1): flg=4 bjn=73179 hint_alias="B"@"SEL$1"

fro(2): flg=4 bjn=73181 hint_alias="C"@"SEL$1"

其中objn表示的正是我们的三个数据表。

--对象映射信息

SQL> select object_name from dba_objects where object_id=73181;

OBJECT_NAME

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

EMP

SQL> select object_name from dba_objects where object_id=73179;

OBJECT_NAME

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

DEPT

SQL> select object_name from dba_objects where object_id=73183;

OBJECT_NAME

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

BONUS

在CBO工作之前，SQL语句要进行一系列的变换过程，目的是为了能够生成更好的执行计划。这个过程叫做Query Transformation。在生成的Trace文件中，我们发现Dept是在QT过程中的一个叫JE（Join Elimination）子环节被删除掉的。

JE: Considering Join Elimination on query block SEL$1 (#0)

*************************

Join Elimination (JE)

*************************

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."BONUS" "A","SCOTT"."DEPT" "B","SCOTT"."EMP" "C" WHERE "A"."ENAME"="C"."ENAME" AND "B"."DEPTNO"="C"."DEPTNO"

JE: cfro: EMP objn:73179 col#:8 dfro:DEPT dcol#:8

Query block (0x9b35d4) before join elimination:

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."BONUS" "A","SCOTT"."DEPT" "B","SCOTT"."EMP" "C" WHERE "A"."ENAME"="C"."ENAME" AND "C"."DEPTNO"="B"."DEPTNO"

JE: eliminate table: DEPT (B)

Registered qb: SEL$739CAFA2 0x9b35d4 (JOIN REMOVED FROM QUERY BLOCK SEL$1; SEL$1; "B"@"SEL$1")

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

QUERY BLOCK SIGNATURE

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

signature (): qb_name=SEL$739CAFA2 nbfros=2 flg=0

fro(0): flg=0 bjn=73183 hint_alias="A"@"SEL$1"

fro(1): flg=0 bjn=73181 hint_alias="C"@"SEL$1"

JE（Join Elimination）是Oracle优化器进行QT过程的一个环节。JE的初衷是消除SQL中一些不必要的连接条件。我们在SQL语句中经常想当然的将一些如order by、join添加上去，但是其实都是没有必要的。JE就是用于消除不必要的连接。

从Trace文件中，JE过程Oracle认为不需要对B表，也就是dept进行连接。虽然它被写入在SQL语句里面。剔除dept之后，优化器重新给SQL进行了register过程，定义了新的qb（Query Block）为SEL$739CAFA2。

新的改写语句为：

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SCOTT"."BONUS" "A","SCOTT"."EMP" "C" WHERE "A"."ENAME"="C"."ENAME" AND "C"."DEPTNO" IS NOT NULL

在新的qb和SQL语句中，我们就只能看到了A和C表了。也就不难理解Oracle执行计划里面没有dept，是因为在QT过程中进行剔除。这部分内容被替换为deptno is not null。

4、约束

那么，是不是Oracle错了呢？

QT（Query Transformation）的动作种类很多，但是每种变换都要遵循一个基本原则就是不改变SQL语句的原意。那么，Oracle为什么认为dept不需要连接？

问题还是处在连接条件上。

"B"."DEPTNO"="C"."DEPTNO"

dept表中deptno为数据表的主键。而emp中的deptno与dept对应是一个典型的多对一的关系，作为主键的dept表中的deptno，唯一性和非空性是有主键约束作为保证。

SQL> select constraint_type, table_name, status from dba_constraints where wner='SCOTT' and constraint_name='PK_DEPT';

CONSTRAINT_TYPE TABLE_NAME STATUS

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

P DEPT ENABLED

优化器的逻辑是这样：既然连接键deptno在dept数据表中是主键，与emp之间是1对n的关系。而且，我们可以看到emp上面的deptno为外键，关联到dept。

SQL> select constraint_type, table_name, status, constraint_name from dba_constraints where wner='SCOTT' and table_name='EMP';

CONSTRAINT_TYPE TABLE_NAME STATUS CONSTRAINT_NAME

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

R EMP ENABLED FK_DEPTNO

P EMP ENABLED PK_EMP

那么，Oracle的线索就构成了：emp中每一个deptno要么为空，要么肯定和dept中的deptno对应。那么，SQL语句中要求的Join，只要保证非空就是满足的了。必然就不需要连接了。

5、验证

那么，如果两个约束被禁用或者不可用。是不是也就消除了关系呢？我们进行试验即可。

SQL> alter table scott.emp modify constraint fk_deptno disable;

Table altered

SQL> select constraint_type, table_name, status, constraint_name from dba_constraints where wner='SCOTT' and table_name='EMP';

CONSTRAINT_TYPE TABLE_NAME STATUS CONSTRAINT_NAME

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

R EMP DISABLED FK_DEPTNO

P EMP ENABLED PK_EMP

重新生成执行计划。

SQL> explain plan for select count(*) from scott.bonus a, scott.dept b, scott.emp c where a.ename=c.ename and b.deptno=c.deptno;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1922630903

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

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

| 0 | SELECT STATEMENT | | 1 | 19 | 6 (17)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 19 | | |

| 2 | NESTED LOOPS | | 1 | 19 | 6 (17)| 00:00:01 |

|* 3 | HASH JOIN | | 1 | 16 | 6 (17)| 00:00:01 |

| 4 | TABLE ACCESS FULL| BONUS | 1 | 7 | 2 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

3 - access("A"."ENAME"="C"."ENAME")

6 - access("B"."DEPTNO"="C"."DEPTNO")

19 rows selected

执行计划已经发生的变化。证明我们的推论正确。

6、结论

进入CBO之后，SQL执行计划生成过程极其复杂、涉及因素众多。其中，描述数据表的约束在很多时候，都是SQL巧妙执行计划的源头。作为我们开发人员和设计人员，不要小看约束、轻视约束。精巧的描述我们的数据，一定可以获得好的回报。

约束Constraint引起CBO执行计划变化一例