[20170516]nvl與非NULL約束.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20170516]nvl與非NULL約束2.txtNull
- 【SQL】15 SQL 約束（Constraints）、NOT NULL 約束、UNIQUE 約束、PRIMARY KEY 約束、FOREIGN KEY 約束、CHECK 約束、DEFAULT約束SQLAINull
- TreeSet的null值與元素型別的約束Null型別
- CHECK約束中的NULL條件Null
- [20121020]主外來鍵約束以及NULL問題.txtNull
- Oracle唯一約束中NULL的處理OracleNull
- [20120410]9i下索引與空值查詢以及非空約束.txt索引
- Oracle外來鍵約束中NULL的處理OracleNull
- 資料遷移判斷非空約束
- NULL和唯一約束UNIQUE的對應關係Null
- not-null約束對執行計劃的影響Null
- [20201201]約束大寫與查詢.txt
- Javaweb-約束-外來鍵約束JavaWeb
- 線上重定義拷貝表結構的NOT NULL約束問題Null
- nvl、nvl2與nullif函式用法區別Null函式
- 運籌優化（十一）--無約束非線性規劃優化
- Django模型之欄位與約束Django模型
- [20240228]約束novalidate.txt
- Oracle定義約束 外來鍵約束Oracle
- SQL約束SQL
- Oracle約束Oracle
- oracle 約束Oracle
- MySQL 約束MySql
- 03約束
- 綜合約束
- [20150727]''與NULL.txtNull
- 非功能性約束之效能（1）-效能銀彈：快取快取
- 運籌優化（十二）--帶約束非線性規劃（NLP）優化
- （10）邏輯綜合新增約束（環境約束）
- MySQL學習筆記——建立與約束MySql筆記
- Oracle約束的學習與測試Oracle
- [20170424]函式COALESCE優於NVL.txt函式
- nvl, nvl2, nullifNull
- [20121028]not in與NULL問題.txtNull
- Unique約束,Primary Key約束與索引的關係學習與測試_20091213.doc索引
- 約束介紹
- 差分約束
- mysql 查詢欄位為null或者非nullMySqlNull