[20191206]nvl与非空约束.txt

[20191206]nvl与非空约束.txt --//优化1个项目,发现开发大量在谓词里面使用nvl函数.感觉这个项目在前期规划混乱.语句谓词里面大量出现 --//类似flag=1,或者nvl(flag,0)=1这样的语句.这样我要优化这样语句必须建立2套索引,是否可以通过约束设置 --//该列非空(当然必须没有null值的情况),取消这个限制,测试看看. 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> create table t as select rownum id ,lpad('a',100,'a') text,0 flag from dual connect by level<=1e5; Table created. SCOTT@book> @desc t Name  Null?    Type ----- -------- ------------- ID             NUMBER TEXT           VARCHAR2(100) FLAG           NUMBER --//flag字段没有约束. SCOTT@book> update t set flag=1 where id=1e5; 1 row updated. SCOTT@book> commit ; Commit complete. SCOTT@book> create index i_t_flag on t(flag); Index created. SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR  COLUMNS flag SIZE 2 ',Cascade => True ,No_Invalidate => false) PL/SQL procedure successfully completed. --//在flag字段上建立直方图. 2.测试1: SCOTT@book> alter session set statistics_level=all ; Session altered. SCOTT@book> select * from t where nvl(flag,0)=1;         ID TEXT                                                                                                       FLAG ---------- ---------------------------------------------------------------------------------------------------- ----------     100000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa          1 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  gg2nkuddjjhyz, child number 1 ------------------------------------- select * from t where nvl(flag,0)=1 Plan hash value: 1601196873 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |   436 (100)|          |      1 |00:00:00.02 |    1567 | |*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |   108 |   436   (1)| 00:00:06 |      1 |00:00:00.02 |    1567 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(NVL("FLAG",0)=1) --//你可以发现在谓词使用nvl(flag,0)=1;E-rows估计也很正确.但是就是不能使用索引. 3.测试2: SCOTT@book> alter table t modify(flag  not null); Table altered. SCOTT@book> select * from t where nvl(flag,0)=1;         ID TEXT                                                                                                       FLAG ---------- ---------------------------------------------------------------------------------------------------- ----------     100000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa          1 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  gg2nkuddjjhyz, child number 1 ------------------------------------- select * from t where nvl(flag,0)=1 Plan hash value: 120143814 ---------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ---------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |          |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       3 | |   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |      1 |   108 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 | |*  2 |   INDEX RANGE SCAN          | I_T_FLAG |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 | ---------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / T@SEL$1    2 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("FLAG"=1) --//可以发现这样可以使用I_T_FLAG,注意看Predicate Information (identified by operation id): --//2 - access("FLAG"=1),不再出现nvl函数. --//当然我必须处理相关记录修改有null替换为缺省值. --//如果写成select * from t where nvl(flag,0)=0;  过滤条件是filter("FLAG"=0). --//实际上开发写代码也没有仔细想想nvl(flag,0)=1,flag是NULL或者0不可能等于1.也可以讲oracle优化器不够智能.修改条件为flag=1. --//仅仅查询nvl(flag,0)=0才需要这样写. --//实际上开发写代码时有一条特殊规定在谓词中使用函数一定要小心.我曾经跟开发开玩笑地讲to_date,trunc之类的函数不是给开发用 --//的.而是给dba用的.可惜这样的错误一犯再犯,一群长不大的群体....