[20170516]nvl與非NULL約束.txt

lfree發表於2017-05-16

[20170516]nvl與非NULL約束.txt

--前幾天做的測試http://blog.itpub.net/267265/viewspace-2137853/,實際上差異沒有這個大,因為第2個多數是常量.
--今天測試nvl與非NULL約束的問題.

1.環境:

SCOTT@book> @ &r/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,'test' name , 0 flag_num ,lpad('0',1,'0') flag_varchar from dual connect by level<=2e4;
Table created.

SCOTT@book> update t set flag_num=1, flag_varchar='1' where id=1e4 or id=2e4;
2 rows updated.

SCOTT@book> commit ;
Commit complete.

--//分析表略.
--//選擇lpad函式,這樣資料型別varchar2.
SCOTT@book> @ &r/desc t
           Name           Null?    Type
           -------------- -------- -------------
    1      ID                      NUMBER
    2      NAME                    CHAR(4)
    3      FLAG_NUM                NUMBER
    4      FLAG_VARCHAR            VARCHAR2(1)


--//首先我一直建議開發對於表示狀態的欄位最好選擇varchar2(1).因為選擇數字除了0佔用1個位元組長度外,其他至少是2.這樣建立對於磁碟空間,以及建立的
--//索引都會減少空間佔用.
--//我還見過使用-1表示狀態的,實際上許多開發不太瞭解oracle一些數字型別的儲存結構.-1佔3個位元組

SCOTT@book> select dump(-1,16) from dual ;
DUMP(-1,16)
---------------------
Typ=2 Len=3: 3e,64,66

2.首先講我遇到的常見錯誤.

SCOTT@book> alter session set statistics_level=all ;
Session altered.

--首先就是隱式轉換的問題
SCOTT@book> select dump(nvl(1,'1'),16) from dual ;
DUMP(NVL(1,'1'),1
-----------------
Typ=2 Len=2: c1,2

SCOTT@book> select dump(nvl('1',1),16) from dual ;
DUMP(NVL('1',1)
---------------
Typ=1 Len=1: 31

--//你可以發現型別向第1個引數型別轉換.

SCOTT@book> select * from t where nvl(flag_varchar,0)=1;
        ID NAME                   FLAG_NUM F
---------- -------------------- ---------- -
     10000 test                          1 1
     20000 test                          1 1

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  0vv5wtp6q8ay5, child number 0
-------------------------------------
select * from t where nvl(flag_varchar,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 |        |       |    17 (100)|          |      2 |00:00:00.01 |      55 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |    200 |  2600 |    17   (0)| 00:00:01 |      2 |00:00:00.01 |      55 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER(NVL("FLAG_VARCHAR",'0'))=1)
23 rows selected.

--//你可以發現這樣情況在開發中很常見.如果寫成如下:

SCOTT@book> select * from t where nvl(flag_varchar,0)='1';
        ID NAME                   FLAG_NUM F
---------- -------------------- ---------- -
     10000 test                          1 1
     20000 test                          1 1

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9y06mwu7wncxx, child number 0
-------------------------------------
select * from t where nvl(flag_varchar,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 |        |       |    17 (100)|          |      2 |00:00:00.01 |      55 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |  10000 |   126K|    17   (0)| 00:00:01 |      2 |00:00:00.01 |      55 |
--------------------------------------------------------------------------------------------------------------------
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_VARCHAR",'0')='1')
--//你仔細看實際上oracle filter變成了 (NVL("FLAG_VARCHAR",'0')='1'),注意0變成'0'.建立開發統一型別這種表示狀態的最好選擇varchar2.
--//這樣在程式設計時就知道是字元型別,帶入引數,以及最後的等於數值選擇字元型別.看看我們的開發團隊就明白這個問題的嚴重性!!而不
--//會出現這樣的情況

3.應用不統一:
--//你可以看到程式在謂詞中兩種情況的出現.
where FLAG_VARCHAR='1'
或者
where nvl(FLAG_VARCHAR,'0')='0'
where nvl(FLAG_VARCHAR,'0')=:B1

--//這樣就必須建立2個索引.實際上如果能使用非空約束,就可以解決這個問題.

SCOTT@book> alter table t modify  FLAG_VARCHAR not null;
Table altered.

SCOTT@book> select * from t where nvl(flag_varchar,0)='1';
        ID NAME                   FLAG_NUM F
---------- -------------------- ---------- -
     10000 test                          1 1
     20000 test                          1 1

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9y06mwu7wncxx, child number 0
-------------------------------------
select * from t where nvl(flag_varchar,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 |        |       |    17 (100)|          |      2 |00:00:00.01 |      55 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |  10000 |   126K|    17   (0)| 00:00:01 |      2 |00:00:00.01 |      55 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("FLAG_VARCHAR"='1')

--//你可以發現非空約束改變了過濾條件,變成了("FLAG_VARCHAR"='1').這樣在真正的應用僅僅建立flag_varchar索引就ok了.

SCOTT@book> create index i_t_flag_varchar on t(flag_varchar);
Index created.

--//在flag_varchar欄位建立直方圖:

SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => nvl('',user),TabName => 't',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag_varchar size 254 ',Cascade => True ,No_Invalidate => false)
PL/SQL procedure successfully completed.

SCOTT@book> select * from t where nvl(flag_varchar,0)='1';
        ID NAME                   FLAG_NUM F
---------- -------------------- ---------- -
     10000 test                          1 1
     20000 test                          1 1

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9y06mwu7wncxx, child number 0
-------------------------------------
select * from t where nvl(flag_varchar,0)='1'
Plan hash value: 932711470
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |        |       |     2 (100)|          |      2 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T                |      1 |      1 |    13 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN          | I_T_FLAG_VARCHAR |      1 |      1 |       |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------------------------------
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_VARCHAR"='1')

--//能很好的使用索引.

4.在需要的資料上建立索引:
--//實際上這個需要dba與開發很好的配合,開發要了解oracle的Btree索引不索引全部是NULL的欄位.利用這個特性索引僅僅需要的資料.例子.

SCOTT@book> create index if_t_flag_varchar on t(decode(flag_varchar,'1','1'));
Index created.

--//這樣僅僅索引flag_varchar='1'的記錄.
SCOTT@book> validate index if_t_flag_varchar;
Index analyzed.

SCOTT@book> @ &r/i
    HEIGHT     BLOCKS NAME          LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
         1          8 IF_T_FLAG_          2          1          26       7996          0          0           0          0           0               0             1
                      VARCHAR


MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
                2        7996         26          1            2                  2.5          0            0              0                0

SCOTT@book> validate index i_t_flag_varchar;
Index analyzed.

SCOTT@book> @ &r/i
    HEIGHT     BLOCKS NAME          LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
         2         48 I_T_FLAG_V      20000         37      260000       7996         36          1         540       8028           0               0             2
                      ARCHAR


MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
            19998      303880     260540         86        10000               5002.5          0            0              1               15

--//上下對比索引的BLOCKS,LF_ROWS,LF_BLKS等數值就明白,函式索引很小.這樣寫語句時寫成如下:
SCOTT@book> select * from t where decode(flag_varchar,'1','1')='1';
        ID NAME         FLAG_NUM F
---------- ---------- ---------- -
     10000 test                1 1
     20000 test                1 1

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  g0dff6822sga3, child number 0
-------------------------------------
select * from t where decode(flag_varchar,'1','1')='1'
Plan hash value: 4162935806
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |      1 |        |       |     3 (100)|          |      2 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T                 |      1 |    200 |  2600 |     3   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | IF_T_FLAG_VARCHAR |      1 |      2 |       |     1   (0)| 00:00:01 |      2 |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("T"."SYS_NC00005$"='1')

--//當然這需要開發這樣寫sql語句.

總結:
--//本來想寫nvl與非NULL約束的問題,遷出一堆別的問題,實際上一個好的團隊需要相互交流與配合,可惜我們開發人員陷入開發專案的爛泥塘...........
--//提交修改的sql語句遙遙無期,根本沒時間修改..

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2139114/,如需轉載,請註明出處,否則將追究法律責任。

相關文章