1 测试现象
CREATE TABLE T1 AS SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS;
CREATE TABLE T2 AS SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS;
创建索引idx1
CREATE INDEX IDX1 ON T1(OBJECT_ID);
EXPLAIN PLAN FOR
SELECT T1.OBJECT_ID FROM T1 LEFT JOIN T2 ON T1.OBJECT_ID=T2.OBJECT_ID;
COMMIT;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 1823443478
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69236 | 1757K| | 415 (1)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 69236 | 1757K| 1696K| 415 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T1 | 69236 | 878K| | 124 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 69414 | 881K| | 124 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"(+))
Note
----- - dynamic statistics used: dynamic sampling (level=2)
如果将列指定为非空
alter table t1 modify object_id not null;
alter table t1 add constraint ooooo check(object_id is not null);
insert into t2 select object_id,object_name from dba_objects where rownum<400
Plan hash value: 2754336310
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 26 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | IDX1 | 1 | 13 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 399 | 5187 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"(+)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
非空约束是字段的一个重要属性。但是，很多时候，数据库表的设计人员似乎并不十分在意这个属性。
最常见的现象就是，除了主键字段外，所有字段都不指定该属性。而在Oracle中，默认是允许为空。
而实际上，优化器在选择执行计划时，非空约束是一个重要的影响因素。
为了说明问题，我们建立以下测试表，然后分别说明非空约束在各种情况下对执行计划和性能的影响。
SQL代码
HELLODBA.COM>create table t_test1 tablespace DEMO as select * from dba_objects;
1.
Table created. 2.
HELLODBA.COM>alter table T_TEST1 add constraint T_TEST1_PK primary key (OBJECT_ID) using index tablespace DEMOTSINX; 3.
Table altered. 4.
HELLODBA.COM>update t_test1 set SUBOBJECT_NAME=OBJECT_NAME where SUBOBJECT_NAME is null; 5.
32072 rows updated. 6.
HELLODBA.COM>commit; 7.
Commit complete. 8.
HELLODBA.COM>desc t_test1 9. Name Null? Type
---------------------------------------------------------------------------------------------------------------------------------10. OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30) 11. SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER 12. DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19) 13. CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE 14. TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7) 15. TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1) 16. SECONDARY VARCHAR2(1)
17. LIO NUMBER谓词评估 在上面表中，字段SUBOBJECT_NAME中不存在空值，但也没有非空约束，再看以下查询，查找该字段的空值记录：
SQL代码
HELLODBA.COM>select * from t_test1 where SUBOBJECT_NAME is null;
1.
no rows selected 2.
Execution Plan 3. ----------------------------------------------------------
Plan hash value: 1883417357 4.
-----------------------------------------------------------------------------5. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------6. | 0 | SELECT STATEMENT | | 1 | 96 | 45 (0)| 00:00:46 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 1 | 96 | 45 (0)| 00:00:46 | 7. -----------------------------------------------------------------------------
8. Predicate Information (identified by operation id):
---------------------------------------------------9.
1 - filter("SUBOBJECT_NAME" IS NULL) 10.
Statistics 11. ----------------------------------------------------------
0 recursive calls 12. 0 db block gets
665 consistent gets 13. 0 physical reads
0 redo size 14. 1048 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client 15. 1 SQL*Net roundtrips to/from client
0 sorts (memory) 16. 0 sorts (disk)
17. 0 rows processed 我们看到，需要对表进行全表扫描（关于索引，随后再讨论）。而如果我们加上非空约束，可以看到执行计划已经性能的变化：
SQL代码
HELLODBA.COM>alter table t_test1 modify SUBOBJECT_NAME not null;
1.
Table altered. 2.
HELLODBA.COM>select * from t_test1 where SUBOBJECT_NAME is null; 3.
no rows selected 4.
Execution Plan 5. ----------------------------------------------------------
Plan hash value: 4146611218 6.
------------------------------------------------------------------------------7. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------8. | 0 | SELECT STATEMENT | | 1 | 96 | 0 (0)| |
|* 1 | FILTER | | | | | | 9. | 2 | TABLE ACCESS FULL| T_TEST1 | 47585 | 4461K| 45 (0)| 00:00:46 |
------------------------------------------------------------------------------10.
Predicate Information (identified by operation id): 11. ---------------------------------------------------
12. 1 - filter(NULL IS NOT NULL)
13.
Statistics 14. ----------------------------------------------------------
0 recursive calls 15. 0 db block gets
0 consistent gets 16. 0 physical reads
0 redo size 17. 1048 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client 18. 1 SQL*Net roundtrips to/from client
0 sorts (memory) 19. 0 sorts (disk)
20. 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
1. Final - All Rows Plan: Best join order: 1
Cost: 0.0000 Degree: 1 Card: 1.0000 Bytes: 4568160 2. Resc: 0.0000 Resc_io: 0.0000 Resc_cpu: 0
3. 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;
1.
Table altered. 2.
HELLODBA.COM>create index t_test1_subo_idx on t_test1(subobject_name) compute statistics; 3.
4. 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;
1.
OWNER OBJECT_NAME SUBOBJECT_NAME 2. ------------------------------ ------------------------------ ------------------------------
SYS ICOL$ BBB 3. SYS I_USER1 BBB
SYS CON$ BBB 4. SYS UNDO$ BBB
SYS I_PROXY_ROLE_DATA$_1 BBB 5. SYS I_OBJ# BBB
SYS PROXY_ROLE_DATA$ BBB 6. SYS I_IND1 BBB
SYS I_CDEF2 BBB 7. SYS C_COBJ# BBB
8. 10 rows selected.
9.
Execution Plan 10. ----------------------------------------------------------
Plan hash value: 4050478946 11.
-----------------------------------------------------------------------------------------------12. | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------13. | 0 | SELECT STATEMENT | | 10 | 560 | | 308 (1)| 00:05:09 |
| 1 | NESTED LOOPS | | 10 | 560 | | 308 (1)| 00:05:09 | 14. | 2 | VIEW | | 10 | 120 | | 298 (1)| 00:04:59 |
| 3 | SORT ORDER BY | | 10 | 160 | 2248K| 298 (1)| 00:04:59 | 15. |* 4 | COUNT STOPKEY | | | | | | |
| 5 | TABLE ACCESS FULL | T_TEST1 | 47585 | 743K| | 45 (0)| 00:00:46 | 16. | 6 | TABLE ACCESS BY USER ROWID| T_TEST1 | 1 | 44 | | 1 (0)| 00:00:02 |
-----------------------------------------------------------------------------------------------17.
Predicate Information (identified by operation id): 18. ---------------------------------------------------
19. 4 - filter(ROWNUM<=10)
20.
Statistics 21. ----------------------------------------------------------
0 recursive calls 22. 0 db block gets
14 consistent gets 23. 0 physical reads
0 redo size 24. 707 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client 25. 2 SQL*Net roundtrips to/from client
1 sorts (memory) 26. 0 sorts (disk)
27. 10 rows processed 但是，查询计划和结果看，语句并没有按照设想的方式执行，得出的数据也不是我们需要的。其原因就在于，由于空值不被索引，优化器无法确认索引数据是否涵盖了所有数据记录，因而它没有选择指定索引。
我们把非空约束加上，执行计划和结果就符合我们的需求了。
SQL代码
HELLODBA.COM>alter table t_test1 modify subobject_name not null;
1.
Table altered. 2.
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 3. order by subobject_name) v where t1.rowid=v.rid;
4. OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ ------------------------------ ------------------------------5. DEMO NO A
DEMO NO A 6. DEMO NO A
SYS ICOL$ BBB 7. SYS I_USER1 BBB
SYS CON$ BBB 8. SYS UNDO$ BBB
SYS C_COBJ# BBB 9. SYS I_OBJ# BBB
SYS PROXY_ROLE_DATA$ BBB 10.
10 rows selected. 11.
12. Execution Plan
----------------------------------------------------------13. Plan hash value: 3198566056
14. ------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 15. ------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 560 | 71 (0)| 00:01:11 | 16. | 1 | NESTED LOOPS | | 10 | 560 | 71 (0)| 00:01:11 |
| 2 | VIEW | | 10 | 120 | 61 (0)| 00:01:01 | 17. |* 3 | COUNT STOPKEY | | | | | |
| 4 | INDEX FULL SCAN | T_TEST1_SUBO_IDX | 47585 | 743K| 61 (0)| 00:01:01 | 18. | 5 | TABLE ACCESS BY USER ROWID| T_TEST1 | 1 | 44 | 1 (0)| 00:00:02 |
------------------------------------------------------------------------------------------------19.
Predicate Information (identified by operation id): 20. ---------------------------------------------------
21. 3 - filter(ROWNUM<=10)
22.
Statistics 23. ----------------------------------------------------------
0 recursive calls 24. 0 db block gets
13 consistent gets 25. 0 physical reads
0 redo size 26. 681 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client 27. 2 SQL*Net roundtrips to/from client
0 sorts (memory) 28. 0 sorts (disk)
29. 10 rows processed非空约束对连接查询的影响 在进行数据关联时，数据集中关联字段是否存在空值也会影响优化器对执行计划的选择。我们再创建一张测试表。
SQL代码
HELLODBA.COM>create table t_test2 tablespace DEMO as select * from dba_tables;
1.
Table created. 2.
HELLODBA.COM>alter table T_TEST2 add constraint T_TEST2_PK primary key (OWNER,TABLE_NAME) using index tablespace DEMOTSINX; 3.
Table altered. 4.
HELLODBA.COM>desc t_test2 5. Name Null? Type
-------------- -------- -----------------6. OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30) 7. TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30) 8. IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8) 9. PCT_FREE NUMBER
PCT_USED NUMBER 10. ... 再将subobject_name的非空约束去掉。
SQL代码
HELLODBA.COM>alter table t_test1 modify subobject_name null;
1.
2. 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);
1.
45135 rows selected. 2.
3. Execution Plan
----------------------------------------------------------4. Plan hash value: 3538907136
5. ------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 6. ------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2379 | 76128 | 51 (0)| 00:00:52 | 7. |* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_TEST1 | 47585 | 1487K| 45 (0)| 00:00:46 | 8. |* 3 | TABLE ACCESS FULL| T_TEST2 | 1 | 18 | 6 (0)| 00:00:07 |
------------------------------------------------------------------------------9.
Predicate Information (identified by operation id): 10. ---------------------------------------------------
11. 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_TEST2" "T2" WHERE
LNNVL("TABLE_NAME"<>:B1))) 12. 3 - filter(LNNVL("TABLE_NAME"<>:B1))
13.
Statistics 14. ----------------------------------------------------------
392 recursive calls 15. 0 db block gets
2217674 consistent gets 16. 0 physical reads
0 redo size 17. 2329590 bytes sent via SQL*Net to client
33473 bytes received via SQL*Net from client 18. 3010 SQL*Net roundtrips to/from client
5 sorts (memory) 19. 0 sorts (disk)
20. 45135 rows processed 可以看到，执行计划通过添加函数LNNVL和NOT EXISTS，对数据进行过滤得到结果，性能相当低。
注意：当逻辑表达是中的操作数可能为空时，LNNVL函数可以判断出该表达式的结果。
我们再把非空约束加上，
