空还是非空——浅谈非空约束对执行计划以及性能的影响

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2011-07-21 08:01:37

分享到 

非空约束是字段的一个重要属性。但是，很多时候，数据库表的设计人员似乎并不十分在意这个属性。最常见的现象就是，除了主键字段外，所有字段都不指定该属性。而在Oracle中，默认是允许为空。     而实际上，优化器在选择执行计划时，非空约束是一个重要的影响因素。为了说明问题，我们建立以下测试表，然后分别说明非空约束在各种情况下对执行计划和性能的影响。 SQL代码 HELLODBA.COM>create table t_test1 tablespace DEMO as select * from dba_objects;      Table created.      HELLODBA.COM>alter table T_TEST1 add constraint T_TEST1_PK primary key (OBJECT_ID) using index  tablespace DEMOTSINX;      Table altered.      HELLODBA.COM>update t_test1 set SUBOBJECT_NAME=OBJECT_NAME where SUBOBJECT_NAME is null;      32072 rows updated.      HELLODBA.COM>commit;      Commit complete.      HELLODBA.COM>desc t_test1    Name          Null?    Type    ---------------------------------------------------------------------------------------------------------------------------------    OWNER         NOT NULL VARCHAR2(30)    OBJECT_NAME   NOT NULL VARCHAR2(30)    SUBOBJECT_NAME         VARCHAR2(30)    OBJECT_ID     NOT NULL NUMBER    DATA_OBJECT_ID         NUMBER    OBJECT_TYPE            VARCHAR2(19)    CREATED       NOT NULL DATE    LAST_DDL_TIME NOT NULL DATE    TIMESTAMP              VARCHAR2(19)    STATUS                 VARCHAR2(7)    TEMPORARY              VARCHAR2(1)    GENERATED              VARCHAR2(1)    SECONDARY              VARCHAR2(1)    LIO                    NUMBER   谓词评估     在上面表中，字段SUBOBJECT_NAME中不存在空值，但也没有非空约束，再看以下查询，查找该字段的空值记录： SQL代码 HELLODBA.COM>select * from t_test1 where SUBOBJECT_NAME is null;      no rows selected      Execution Plan   ----------------------------------------------------------   Plan hash value: 1883417357      -----------------------------------------------------------------------------   | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |   -----------------------------------------------------------------------------   |   0 | SELECT STATEMENT  |         |     1 |    96 |    45   (0)| 00:00:46 |   |*  1 |  TABLE ACCESS FULL| T_TEST1 |     1 |    96 |    45   (0)| 00:00:46 |   -----------------------------------------------------------------------------      Predicate Information (identified by operation id):   ---------------------------------------------------         1 - filter("SUBOBJECT_NAME" IS NULL)      Statistics   ----------------------------------------------------------             0  recursive calls             0  db block gets           665  consistent gets             0  physical reads             0  redo size          1048  bytes sent via SQL*Net to client           374  bytes received via SQL*Net from client             1  SQL*Net roundtrips to/from client             0  sorts (memory)             0  sorts (disk)             0  rows processed       我们看到，需要对表进行全表扫描（关于索引，随后再讨论）。而如果我们加上非空约束，可以看到执行计划已经性能的变化： SQL代码 HELLODBA.COM>alter table t_test1 modify SUBOBJECT_NAME not null;      Table altered.      HELLODBA.COM>select * from t_test1 where SUBOBJECT_NAME is null;      no rows selected      Execution Plan   ----------------------------------------------------------   Plan hash value: 4146611218      ------------------------------------------------------------------------------   | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |   ------------------------------------------------------------------------------   |   0 | SELECT STATEMENT   |         |     1 |    96 |     0   (0)|          |   |*  1 |  FILTER            |         |       |       |            |          |   |   2 |   TABLE ACCESS FULL| T_TEST1 | 47585 |  4461K|    45   (0)| 00:00:46 |   ------------------------------------------------------------------------------      Predicate Information (identified by operation id):   ---------------------------------------------------         1 - filter(NULL IS NOT NULL)         Statistics   ----------------------------------------------------------             0  recursive calls             0  db block gets             0  consistent gets             0  physical reads             0  redo size          1048  bytes sent via SQL*Net to client           374  bytes received via SQL*Net from client             1  SQL*Net roundtrips to/from client             0  sorts (memory)             0  sorts (disk)             0  rows processed       注意到，在全表扫描之前，增加了一个filter，而fileter的表达式是NULL IS NOT NULL，其逻辑结果是FALSE，因此，实际上其子操作（全表扫描）并未执行。相应的，请性能数据里面CR为0。     之所以优化器会为执行计划增加这样一个filter，是因为优化器在做查询转换（Query Transformation）时，会将非空约束作为参照条件之一，对where子句的谓词做逻辑结果评估，如果评估结果为false，则会增加一个这样的filter，以避免执行一些高代价的操作。从10053跟踪文件中，可以看到这对于优化器对执行计划代价估算的影响： SQL代码 Cdn, Cost adjusted (to ~ 0) as where clause evalutes to FALSE   Final - All Rows Plan:  Best join order: 1     Cost: 0.0000  Degree: 1  Card: 1.0000  Bytes: 4568160     Resc: 0.0000  Resc_io: 0.0000  Resc_cpu: 0     Resp: 0.0000  Resp_io: 0.0000  Resc_cpu: 0   非空约束对索引选择的影响     我们知道，Oracle中B*树索引中不存在空键值，即在表的数据记录中，如果索引中所有字段都为空，则该记录不会被构建到索引树中。也就是说，如果索引字段上没有非空约束，则表记录与索引记录不是完全映射的。     我们先去掉subobject_name上的非空约束，并在上面建立索引： SQL代码 HELLODBA.COM>alter table t_test1 modify subobject_name null;      Table altered.      HELLODBA.COM>create index t_test1_subo_idx on t_test1(subobject_name) compute statistics;      Index created.       执行以下语句，以获取subobject_name最小的10条记录。为了提高效率，我们希望直接从索引中直接读取前10条ROWID（索引数据已经按照subobject_name排序），然后根据ROWID获取数据记录： SQL代码 HELLODBA.COM>select owner,object_name,subobject_name from t_test1 t1, (select /*+index(t t_test1_subo_idx)*/rowid rid from t_test1 t where rownum<=10order by subobject_name) v where t1.rowid=v.rid;      OWNER                          OBJECT_NAME                    SUBOBJECT_NAME   ------------------------------ ------------------------------ ------------------------------   SYS                            ICOL$                          BBB   SYS                            I_USER1                        BBB   SYS                            CON$                           BBB   SYS                            UNDO$                          BBB   SYS                            I_PROXY_ROLE_DATA$_1           BBB   SYS                            I_OBJ#                         BBB   SYS                            PROXY_ROLE_DATA$               BBB   SYS                            I_IND1                         BBB   SYS                            I_CDEF2                        BBB   SYS                            C_COBJ#                        BBB      10 rows selected.         Execution Plan   ----------------------------------------------------------   Plan hash value: 4050478946      -----------------------------------------------------------------------------------------------   | Id  | Operation                   | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |   -----------------------------------------------------------------------------------------------   |   0 | SELECT STATEMENT            |         |    10 |   560 |       |   308   (1)| 00:05:09 |   |   1 |  NESTED LOOPS               |         |    10 |   560 |       |   308   (1)| 00:05:09 |   |   2 |   VIEW                      |         |    10 |   120 |       |   298   (1)| 00:04:59 |   |   3 |    SORT ORDER BY            |         |    10 |   160 |  2248K|   298   (1)| 00:04:59 |   |*  4 |     COUNT STOPKEY           |         |       |       |       |            |          |   |   5 |      TABLE ACCESS FULL      | T_TEST1 | 47585 |   743K|       |    45   (0)| 00:00:46 |   |   6 |   TABLE ACCESS BY USER ROWID| T_TEST1 |     1 |    44 |       |     1   (0)| 00:00:02 |   -----------------------------------------------------------------------------------------------      Predicate Information (identified by operation id):   ---------------------------------------------------         4 - filter(ROWNUM<=10)         Statistics   ----------------------------------------------------------             0  recursive calls             0  db block gets            14  consistent gets             0  physical reads             0  redo size           707  bytes sent via SQL*Net to client           385  bytes received via SQL*Net from client             2  SQL*Net roundtrips to/from client             1  sorts (memory)             0  sorts (disk)            10  rows processed       但是，查询计划和结果看，语句并没有按照设想的方式执行，得出的数据也不是我们需要的。其原因就在于，由于空值不被索引，优化器无法确认索引数据是否涵盖了所有数据记录，因而它没有选择指定索引。     我们把非空约束加上，执行计划和结果就符合我们的需求了。 SQL代码 HELLODBA.COM>alter table t_test1 modify subobject_name not null;      Table altered.      HELLODBA.COM>select owner,object_name,subobject_name from t_test1 t1, (select /*+index(t t_test1_subo_idx)*/rowid rid from t_test1 t where rownum<=10   order by subobject_name) v where t1.rowid=v.rid;      OWNER                          OBJECT_NAME                    SUBOBJECT_NAME   ------------------------------ ------------------------------ ------------------------------   DEMO                           NO                             A   DEMO                           NO                             A   DEMO                           NO                             A   SYS                            ICOL$                          BBB   SYS                            I_USER1                        BBB   SYS                            CON$                           BBB   SYS                            UNDO$                          BBB   SYS                            C_COBJ#                        BBB   SYS                            I_OBJ#                         BBB   SYS                            PROXY_ROLE_DATA$               BBB      10 rows selected.         Execution Plan   ----------------------------------------------------------   Plan hash value: 3198566056      ------------------------------------------------------------------------------------------------   | Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |   ------------------------------------------------------------------------------------------------   |   0 | SELECT STATEMENT            |                  |    10 |   560 |    71   (0)| 00:01:11 |   |   1 |  NESTED LOOPS               |                  |    10 |   560 |    71   (0)| 00:01:11 |   |   2 |   VIEW                      |                  |    10 |   120 |    61   (0)| 00:01:01 |   |*  3 |    COUNT STOPKEY            |                  |       |       |            |          |   |   4 |     INDEX FULL SCAN         | T_TEST1_SUBO_IDX | 47585 |   743K|    61   (0)| 00:01:01 |   |   5 |   TABLE ACCESS BY USER ROWID| T_TEST1          |     1 |    44 |     1   (0)| 00:00:02 |   ------------------------------------------------------------------------------------------------      Predicate Information (identified by operation id):   ---------------------------------------------------         3 - filter(ROWNUM<=10)         Statistics   ----------------------------------------------------------             0  recursive calls             0  db block gets            13  consistent gets             0  physical reads             0  redo size           681  bytes sent via SQL*Net to client           385  bytes received via SQL*Net from client             2  SQL*Net roundtrips to/from client             0  sorts (memory)             0  sorts (disk)            10  rows processed   非空约束对连接查询的影响     在进行数据关联时，数据集中关联字段是否存在空值也会影响优化器对执行计划的选择。我们再创建一张测试表。 SQL代码 HELLODBA.COM>create table t_test2 tablespace DEMO as select * from dba_tables;      Table created.      HELLODBA.COM>alter table T_TEST2 add constraint T_TEST2_PK primary key (OWNER,TABLE_NAME) using index  tablespace DEMOTSINX;      Table altered.      HELLODBA.COM>desc t_test2    Name          Null?    Type   -------------- -------- -----------------    OWNER         NOT NULL VARCHAR2(30)    TABLE_NAME    NOT NULL VARCHAR2(30)    TABLESPACE_NAME        VARCHAR2(30)    CLUSTER_NAME           VARCHAR2(30)    IOT_NAME               VARCHAR2(30)    STATUS                 VARCHAR2(8)    PCT_FREE               NUMBER    PCT_USED               NUMBER   ...       再将subobject_name的非空约束去掉。 SQL代码 HELLODBA.COM>alter table t_test1 modify subobject_name null;      Table altered.       我们通过以下语句查找t_test1中subobject_name不为table_name的数据： SQL代码 HELLODBA.COM>select t1.owner, t1.object_name, t1.subobject_name from t_test1 t1 where subobject_name not in (select table_name from t_test2 t2);      45135 rows selected.         Execution Plan   ----------------------------------------------------------   Plan hash value: 3538907136      ------------------------------------------------------------------------------   | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |   ------------------------------------------------------------------------------   |   0 | SELECT STATEMENT   |         |  2379 | 76128 |    51   (0)| 00:00:52 |   |*  1 |  FILTER            |         |       |       |            |          |   |   2 |   TABLE ACCESS FULL| T_TEST1 | 47585 |  1487K|    45   (0)| 00:00:46 |   |*  3 |   TABLE ACCESS FULL| T_TEST2 |     1 |    18 |     6   (0)| 00:00:07 |   ------------------------------------------------------------------------------      Predicate Information (identified by operation id):   ---------------------------------------------------         1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_TEST2" "T2" WHERE                 LNNVL("TABLE_NAME"<>:B1)))      3 - filter(LNNVL("TABLE_NAME"<>:B1))         Statistics   ----------------------------------------------------------           392  recursive calls             0  db block gets       2217674  consistent gets             0  physical reads             0  redo size       2329590  bytes sent via SQL*Net to client         33473  bytes received via SQL*Net from client          3010  SQL*Net roundtrips to/from client             5  sorts (memory)             0  sorts (disk)         45135  rows processed       可以看到，执行计划通过添加函数LNNVL和NOT EXISTS，对数据进行过滤得到结果，性能相当低。     注意：当逻辑表达是中的操作数可能为空时，LNNVL函数可以判断出该表达式的结果。     我们再把非空约束加上， SQL代码 HELLODBA.COM>alter table t_test1 modify subobject_name not null;      Table altered.      HELLODBA.COM>select /*+ordered*/t1.owner, t1.object_name, t1.subobject_name from t_test1 t1 where not exists (select 1 from t_test2 t2 where t1.subobject_name = t2.table_name);      45135 rows selected.         Execution Plan   ----------------------------------------------------------   Plan hash value: 3049160152      ---------------------------------------------------------------------------------------   | Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |   ---------------------------------------------------------------------------------------   |   0 | SELECT STATEMENT    |         |     1 |    50 |       |   475   (1)| 00:07:56 |   |   1 |  MERGE JOIN ANTI    |         |     1 |    50 |       |   475   (1)| 00:07:56 |   |   2 |   SORT JOIN         |         | 47585 |  1487K|  4136K|   458   (1)| 00:07:39 |   |   3 |    TABLE ACCESS FULL| T_TEST1 | 47585 |  1487K|       |    45   (0)| 00:00:46 |   |*  4 |   SORT UNIQUE       |         |  2070 | 37260 |   120K|    17   (6)| 00:00:18 |   |   5 |    TABLE ACCESS FULL| T_TEST2 |  2070 | 37260 |       |     6   (0)| 00:00:07 |   ---------------------------------------------------------------------------------------      Predicate Information (identified by operation id):   ---------------------------------------------------         4 - access("T1"."SUBOBJECT_NAME"="T2"."TABLE_NAME")          filter("T1"."SUBOBJECT_NAME"="T2"."TABLE_NAME")         Statistics   ----------------------------------------------------------            41  recursive calls           141  db block gets           882  consistent gets          1432  physical reads             0  redo size       2050667  bytes sent via SQL*Net to client         33473  bytes received via SQL*Net from client          3010  SQL*Net roundtrips to/from client             0  sorts (memory)             2  sorts (disk)         45135  rows processed       可以看到执行计划通过ANTI-JOIN获取我们需要的数据，性能大为改善。     我们知道，对于空值的逻辑判断，只能通过IS NULL或IS NOT NULL进行判断，除此之外，一旦表达式中含有NULL值，结果始终为空。这也就是空值的存在不会影响JOIN或SEMI-JOIN的原因。 SQL代码 HELLODBA.COM>select 1 from dual where null='';      no rows selected      HELLODBA.COM>select 1 from dual where null=null;      no rows selected      HELLODBA.COM>select 1 from dual where null is null;               1   ----------            1       ANTI-JOIN，通过关联方法（NESTED-LOOP、MERGE、HASH）判断记录是否符合条件，一旦发现两边记录可以关联上，则判定不符合要求，可以放弃对一个数据集中的剩余数据的判断，因而能提高性能。而关联是同等价匹配（=）实现的，不适合空值数据。因此，关联字段可能存在空值时，无法采用ANTI-JOIN。     注意：无论是否存在空值，NOT EXISTS都可以采用ANTI-JOIN，但是它的逻辑结果与NOT IN并不等价。在以下例子中，NOT EXISTS和NOT IN的结果不相同： SQL代码 HELLODBA.COM>alter table t_test1 modify subobject_name null;      Table altered.      HELLODBA.COM>update t_test1 set subobject_name=null where rownum <=10;      10 rows updated.      HELLODBA.COM>select t1.owner, t1.object_name, t1.subobject_name from t_test1 t1 where subobject_name not in (select table_name from t_test2 t2);      45129 rows selected.      Execution Plan   ----------------------------------------------------------   Plan hash value: 3538907136      ------------------------------------------------------------------------------   | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |   ------------------------------------------------------------------------------   |   0 | SELECT STATEMENT   |         |  2379 | 76128 |    51   (0)| 00:00:52 |   |*  1 |  FILTER            |         |       |       |            |          |   |   2 |   TABLE ACCESS FULL| T_TEST1 | 47585 |  1487K|    45   (0)| 00:00:46 |   |*  3 |   TABLE ACCESS FULL| T_TEST2 |     1 |    18 |     6   (0)| 00:00:07 |   ------------------------------------------------------------------------------      Predicate Information (identified by operation id):   ---------------------------------------------------         1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_TEST2" "T2" WHERE                 LNNVL("TABLE_NAME"<>:B1)))      3 - filter(LNNVL("TABLE_NAME"<>:B1))         Statistics   ----------------------------------------------------------           745  recursive calls           216  db block gets       2217413  consistent gets            13  physical reads             0  redo size       2329442  bytes sent via SQL*Net to client         33473  bytes received via SQL*Net from client          3010  SQL*Net roundtrips to/from client            26  sorts (memory)             0  sorts (disk)         45129  rows processed      HELLODBA.COM>select t1.owner, t1.object_name, t1.subobject_name from t_test1 t1 where not exists (select 1 from t_test2 t2 where t1.subobject_name = t   2.table_name);      45139 rows selected.      Execution Plan   ----------------------------------------------------------   Plan hash value: 2383621862      --------------------------------------------------------------------------------   | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |   --------------------------------------------------------------------------------   |   0 | SELECT STATEMENT     |         |     1 |    50 |    52   (2)| 00:00:52 |   |*  1 |  HASH JOIN RIGHT ANTI|         |     1 |    50 |    52   (2)| 00:00:52 |   |   2 |   TABLE ACCESS FULL  | T_TEST2 |  2070 | 37260 |     6   (0)| 00:00:07 |   |   3 |   TABLE ACCESS FULL  | T_TEST1 | 47585 |  1487K|    45   (0)| 00:00:46 |   --------------------------------------------------------------------------------      Predicate Information (identified by operation id):   ---------------------------------------------------         1 - access("T1"."SUBOBJECT_NAME"="T2"."TABLE_NAME")         Statistics   ----------------------------------------------------------            49  recursive calls           360  db block gets          4130  consistent gets             0  physical reads             0  redo size       2329713  bytes sent via SQL*Net to client         33484  bytes received via SQL*Net from client          3011  SQL*Net roundtrips to/from client             2  sorts (memory)             0  sorts (disk)         45139  rows processed      HELLODBA.COM>rollback;      Rollback complete.       --- Fuyuncat ---