分享好友 站长动态首页 网站导航

Oracle免费的便捷Web应用开发框架二则

2022-03-31 09:45 · 头闻号数据库

问题与分析

首先,我们看一张图,我称之为 “四大皆空”,此图来源于 刘晨 的视频分享 《Oracle中新增字段的点点滴滴》,很有意思,从图中可以发现两段 SQL,看看一下这张图有什么奇怪的地方!

SQL 分析

第一段 SQL:

SQL> select * from test where c1 is null;
no rows selected

SQL> select * from test where c1 is not null;
ID NAME C1
------ ------- ---
1 a

看完第一段 SQL,是不是已经产生疑惑 ? 先不急,接着看第二段 SQL!

第二段 SQL:

SQL> select dump(c1) as d from test;
D
-------
NULL

SQL> select nvl(c1,'is null') as c1 from test;
C1
-------
IS NULL

看完第二段 SQL,得出统一的结论就是:c1 字段值为空。

根据上面两段 SQL 的结论,也就有了上图中的 where PK dump/nvl? 的疑问,那么到底是什么导致的这个问题呢?

猜测与实践

首先,这明显不是一个正常的操作能够导致的问题,所以首先排除插入空值到非空字段的情况,需要从其他的思路的进行探讨。

通过 dbms_metadata.get_ddl 函数获取 test 表结构的定义:

select dbms_metadata.get_ddl('TABLE', 'TEST') from dual;
DBMS_metaDATA.GET_DDL('TABLE','TEST')
--------------------------------------------------------------------------------
CREATE TABLE "TEST"."TEST"
( "ID" NUMBER,
"NAME" VARCHAR2(8) DEFAULT 'a',
"C1" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

小知识拓展:

可以发现,c1 字段是非空字段,且默认值为空。 为什么 Oracle 会允许空值插入到非空约束字段中?

想要搞明白原因,光靠猜测是没有用的,实践是检验真理的唯一标准。

猜测一

有没有可能是,插入记录时有非空约束的列默认为空导致:

SQL> insert into TEST (id, name) values (1, 'a');
insert into TEST (id, name) values (1, 'a')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."TEST"."C1")

可以看到插入报错了,说明这个思路是错的,此路不通。

猜测二

按理来说,Oracle 这么多版本的更新迭代之后,应该不会在 11G 版本还出现这种问题,综上所述,猜测可能是 11G 的新特性导致的 BUG。

查询官方文档中的 11G 新特性 Enhanced ADD COLUMN Functionality 可以发现:

在 11G 版本中,当添加带有默认值且非空约束的列时,不直接更新当前表的所有记录的该列默认值,而是将数据存储到数据字典中的 sys.col$ 表中,后续执行 DML 操作时会自动更新该列默认值。

接下来就是用新特性来测试一下,首先创建 TEST 表,不包含 C1 字段:

CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(8) DEFAULT 'a');  

手动添加 c1 列(非空约束+默认值为空):

alter table TEST add c1 varchar2(8) default '' not null;

再次查询:

SQL> select * from test where c1 is not null;
ID NAME C1
------ ------- ---
1 a

破案了,函数是对的,c1 字段值在默认的情况下确实为空,NOT NULL 列的默认值为 NULL,如果不指定默认值那么就相当于默认值为 NULL。

深入与研究

上面通过猜测和实践得出了问题的原因,但还是有些不明所以:

Where 错了吗?

通过 ”四大皆空“ 图看起来,使用 Where 条件返回了错误的数据,CBO 那么聪明,执行计划判断不出来?

第一个 SQL:

SQL> select * from test where c1 is null;
no rows selected

分析:当查询条件 c1 为空时,CBO 给出一个谓词 filter 过滤条件 NULL IS NOT NULL,这意味着查询条件恒假,当一个查询条件恒假的时候,Oracle 不需要真正执行语句,所以看到 Cost(%CPU) 为 0,所以当一个查询条件明显的违反表中的约束条件时,Oracle 并不会去执行这个查询语句,而是直接返回了 0 条记录。

第二个 SQL 的执行计划:

SQL> select * from test where c1 is not null;
ID NAME C1
------ ------- ---
1 a

分析:当查询条件为 c1 不为空时,执行计划中并没有 filter 谓词条件,为什么呢?因为 c1 字段是非空约束,所以 CBO 判读 c1 is not null 这个查询条件是恒真的,也就不需要过滤,直接返回所有的数据。

结论: 简单的说,导致这个问题的原因是由于错误的数据存储于表中,而这导致了 CBO 在判断时出现了错误,导致和预期相反的结果返回,所以 where 并没有错误,是新特性的 BUG 导致 CBO 的判断错误。

新特性详解

Oracle 为什么要引入这个新特性?我们使用 3 种情况的分析一下!

实践演示

准备测试数据:

create table test(id number,name varchar2(1));
insert into test values(1,a);
insert into test values(2,b);
commit;
select * from test;

通过 dump 操作来查看数据的实时情况:

select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from test;
alter system dump datafile 4 block 173109;

第一种情况:增加一个字段,不带默认值,不带非空约束:

alter table test add a1 varchar2(1);
desc test

当为表增加一个不带默认值,不带非空约束的字段时,已存记录的数据块中不会立刻存储该新增字段:

只有当更新字段或插入数据的时候,数据块中才会实际存储:

更新操作:

插入操作:

第二种情况:增加一个字段,带默认值,不带非空约束

注意:针对这种情况,12C 引入了新特性:metaData-only DEFAULT Column Values for NULL Columns:

alter table test add a2 varchar2(1) default 'a';
desc test

Oracle 11g,新增一个带默认值,不带非空约束的字段,会立刻在表的数据块中增加该字段:

并执行全表更新的操作,将该值更新为默认值,DDL操作的执行时间和表的数据量相关:

第三种情况:增加一个字段,带默认值,带非空约束

注意:针对这种情况,11G 引入了新特性:Enhanced ADD COLUMN Functionality!

alter table test add a2 varchar2(1) default 'a' not null;
desc test

Oracle 11g,新增一个带默认值,带非空约束的字段,已存记录的数据块中不会立刻存储该新增字段:

而是将其作为元数据存储在数据字典中的 sys.col$ 中:

同时在 sys.ecol$ 中可以看到:

但是,当改变新增列的默认值时,sys.ecol$ 的数据不会实时变化,仅存储第一次增加列时的默认值:

只有当更新字段或插入数据的时候,数据块才会实际存储:

通过这种优化,缩短了DDL执行时间,这就是 Oracle 11G 引入 Enhanced ADD COLUMN Functionality 新特性的原因。

最后

值得一提的是,Oracle 在 12C 以后已经修复了这个 BUG,增加了如下判断(DEFAULT 为 NULL 是禁止的):

Oracle 学习路漫漫,茫茫文档需要看,直觉前路要变宽,到头还被 BUG 绊!

本文参考资料:

免责声明:本平台仅供信息发布交流之途,请谨慎判断信息真伪。如遇虚假诈骗信息,请立即举报

举报
反对 0
打赏 0
更多相关文章

评论

0

收藏

点赞