[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用的.可惜这样的错误一犯再犯,一群长不大的群体....