[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语句遥遥无期,根本没时间修改..

时间： 2024-07-29 13:02:44

[20170516]nvl与非NULL约束.txt的相关文章

[20170516]nvl与非NULL约束2.txt

[20170516]nvl与非NULL约束2.txt --//接着上午的测试看看COALESCE看看过滤的情况. 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------------------------------------------------------------

[20120410]9i下索引与空值查询以及非空约束.txt

[20120410]9i下索引与空值查询以及非空约束.txt 前几天在优化9i的一条sql语句时,发现一个奇怪的现象,查询条件是is null,发现竟然可以使用索引,感觉很奇怪,再仔细看原来查询字段存在一个非空约束,对比了10g以及11g的版本,感觉10g与11g改进不少. 测试如下: 1.建立测试环境: SQL> select * from v$version; BANNER -----------------------------------------------------------

[20121020]主外键约束以及NULL问题.txt

[20121020]主外键约束以及NULL问题.txt 主外键约束可以一定程度保证数据完整性,但是如果外键输入的是NULL,情况会如何呢? SQL> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 6

新增非空约束字段在不同版本中的演进

开发提了一个数据库变更需求,新增一字段,没有NOT NULL非空约束,但有默认值为NULL.看起来有些奇怪,因为若字段允许NULL,其默认值就是NULL,不用显示声明,可以创建一个无DEFAULT NULL的新增字段再查看desc表结构,就可以证明这点. 基于这问题,引申出的NOT NULL字段问题还有不少,也是比较容易忽视的一些细节,例如杨长老最近连续发表过两篇关于NOT NULL字段的文章确实很有启发, 非空字段空值对查询的影响 http://yangtingkun.net/?p=1481

oracle-初学ORACLE数据库，关于非空约束问题请教大家。

问题描述 初学ORACLE数据库,关于非空约束问题请教大家. 建表的时候有些字段设置了not null,但是插入记录时并没有哪一行插入记录,为什么会报错了?01400, 比如所有A/B/C三个字段,A设置类not null 但是我要插入记录时并不需要给A插入记录,只想给B/C插入记录.insert int table_name(b,c) values(value1,values2); 这样,报错了,无法将null插入A这个很不理解,请求大神,帮助一下. 解决方案 你已经把A设置为非空了,当插入这

oracle在线重定义拷贝表结构的NOT NULL约束问题

以前测试和使用的时候还真的没发现这个问题,一直认为COPY_TABLE_DEPENDENTS会自动过滤NOT NULL约束. 然而事实并发如此,如果打算使用COPY_TABLE_DEPENDENTS过程复制索引.约束以及权限等相关对象,那么在建立目标表的时候,即使是NOT NULL约束都应该避免,否则NOT NULL约束的存在会引发错误: SQL> CREATE TABLE T AS 2  SELECT ROWNUM ID, OBJECT_NAME NAME, OBJECT_TYPE TYPE

在DataGridView中如何进行唯一性和非空约束处理

问题描述 在数据库中的物理表上分别为两个字段设置了NOTNULL和UNIQUE约束数据在UI上以DataGridView呈现,用户可以在其中进行添加.删除.修改操作当用户的输入违反唯一性和非空性约束时,我应该在哪处理?如何处理? 解决方案 解决方案二:在用户添加,修改的时候验证,是空的就不让他写入.唯一性就在提交时根据他输入的值去数据库看下有没有,有了也不让操作.

（再贴）在DataGridView中如何进行唯一性和非空约束处理

问题描述 在数据库中的物理表上分别为两个字段设置了NOTNULL和UNIQUE约束数据在UI上以DataGridView呈现,用户可以在直接在其中添加删除修改当用户的输入违反唯一性和非空性约束时,我应该在哪里处理?如何处理?我的意思是说:1.事先就为DataGridView的数据源中的字段设置好唯一性和非空约束,然后捕获DataGridView的异常2.还是不为DataGridView的数据源中的字段设置这些,而是当用户点击确定时,用代码逐行严正数据源中的数据是否符合约束然后再写入数据库 解决方

SQL NOT NULL 约束语法与not null实例

SQL NOT NULL 约束语法与not null实例 如果我们创建一个表 CREATE TABLE aa ( Id_P int NOT NULL, LName varchar(250) NOT NULL, FName varchar(25), Ass varchar(25), C varchar(205) ) NOT NULL 约束强制列不接受 NULL 值. NOT NULL 约束强制字段始终包含值.这意味着,如果不向字段添加值,就无法插入新纪录或者更新记录. 下面的 SQL 语句强制 "