[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用的.可惜這樣的錯誤一犯再犯,一群長不大的群體....