AI 助理
备案控制台
开发者社区 数据库 文章 正文

[20170516]nvl与非NULL约束.txt

2017-05-16 874
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20170516]nvl与非NULL约束.txt --前几天做的测试http://blog.itpub.net/267265/viewspace-2137853/,实际上差异没有这个大,因为第2个多数是常量.

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

文章标签：
索引
关系型数据库
Oracle
SQL
关键词：
nvl null
lfreeali
目录
相关文章
龙腾九州
|
8月前
|
关系型数据库 MySQL
非空约束：not null
非空约束：not null。
龙腾九州
59 5
米果粒
|
6月前
|
SQL
SQL NOT NULL 约束
【7月更文挑战第18天】SQL NOT NULL 约束。
米果粒
59 6
龙腾九州
|
6月前
|
SQL
SQL NOT NULL 约束
【7月更文挑战第16天】SQL NOT NULL 约束。
龙腾九州
40 3
听白
|
7月前
|
SQL
SQL NOT NULL 约束
SQL NOT NULL 约束
听白
51 1
听白
|
7月前
|
SQL
SQL NOT NULL 约束
SQL NOT NULL 约束
听白
49 6
好奇的菜鸟
|
7月前
|
SQL 关系型数据库 MySQL
MySQL外键约束行为解析：CASCADE, NO ACTION, RESTRICT, SET NULL
MySQL外键约束行为解析：CASCADE, NO ACTION, RESTRICT, SET NULL
好奇的菜鸟
673 0
极客小俊
|
关系型数据库 MySQL 数据库
MySQL设置 not null 约束 提示 invalid use of null value 问题出现原因及解决
解决方案
极客小俊
479 0
MySQL设置 not null 约束 提示 invalid use of null value 问题出现原因及解决
DyeDD
|
关系型数据库 MySQL 开发者
MySQL 字段约束 null, not null, default, auto_increment
前言：转载，觉得有用就发了一遍 在 MySQL 中，每个字段定义都包含附加约束或者修饰符，这些可以用来增加对所输入数据的约束。 今天我们就来看一下 MySQL 的字段约束： NULL 和 NOT NULL 修饰符、DEFAULT 修饰符，AUTO_INCREMENT 修饰符。
DyeDD
210 0
didiplus
|
SQL 关系型数据库 MySQL
MySQL非空约束(NOT NuLL)
MySQL非空约束(NOT NuLL)
didiplus
292 0
会飞的鱼-blog
|
SQL 关系型数据库 MySQL
列的完整性约束——设置表字段的非空约束（NOT NULL, NK）
列的完整性约束——设置表字段的非空约束（NOT NULL, NK）
会飞的鱼-blog
215 0

热门文章

最新文章

  • 1
    【Bug合集】——Java大小写引起传参失败，获取值为null的解决方案
  • 2
    String为null
  • 3
    You have version null and I want version 8
  • 4
    php中empty()，isset()，is_null()，==，===区别
  • 5
    typeof升级版，可以识别出array、object、null、nan、[]、{}
  • 6
    Mysql的NULL的一个注意点
  • 7
    解决mybatis-plus日期显示为null的问题
  • 8
    一个坑：java.sql.ResultSet.getInt==》the column value; if the value is SQL NULL, the value returned is 0
  • 9
    深入详解SQL中的Null
  • 10
    oracle中的空值null
  • 1
    Android 高版本 packageManager.getPackageArchiveInfo 总是返回null
    312
  • 2
    Spring Boot单元测试报错java.lang.IllegalStateException: Could not load TestContextBootstrapper [null]
    199
  • 3
    键值的 key 和 value 允许为null吗
    93
  • 4
    DataWorks操作报错合集之DataWorks提交失败: 提交节点的源码内容到TSP(代码库)失败:"skynet_packageid is null，该怎么解决
    139
  • 5
    java Map删除值为null的元素
    166
  • 6
    【Web 前端】undefined 和 null 区别？
    939
  • 7
    MaxCompute产品使用合集之需要将多个字段concat起来，有时候遇到null值，该怎么做
    162
  • 8
    云原生数据仓库产品使用合集之在云数据仓库ADB中，GROUP BY操作中出现NULL值,如何解决
    965
  • 9
    为什么HashMap的键值可以为null，而ConcurrentHashMap不行？
    99
  • 10
    Trying to access array offset on value of type null
    1903

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    开通oss服务