ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 约束Constraint引起CBO执行计划变化一例

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

原创 Linux操作系统 作者:realkid4 时间:2013-08-20 22:56:27 0 删除 编辑

 

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

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

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

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

|   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

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

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

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

|   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

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

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

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

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

|   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巧妙执行计划的源头。作为我们开发人员和设计人员,不要小看约束、轻视约束。精巧的描述我们的数据,一定可以获得好的回报。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-768928/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7632246