PowerPoint Presentation

Size: px

Start display at page:

Download "PowerPoint Presentation"

但麻
4 years ago
Views:

1 云和恩墨成就所托 YUNHE ENMO (BEIJING) TECHNOLOGY CO.,LTD 从菜鸟到专家成长系列之一大梦初醒云和恩墨 ( 北京 ) 信息技术有限公司 ACE: 侯圣文李轶楠杨廷琨张乐奕邮箱 :training@enmotech.com

故事背景 : 使用同步软件, 把源端数据同步到目标端时, 产生了报错 Release 11.2.0.3.

2 故事背景 : 使用同步软件, 把源端数据同步到目标端时, 产生了报错 Release 报错 : ERROR at line 1: ORA-01400: cannot insert NULL into ("TEST"."T_PROD"."TYPE") 人物角色 : 甲方老总杨廷琨乙方主管张乐奕乙方驻场 DBA 李轶楠恩墨学院院长侯圣文

3 小小 DBA: 哎呦, 这个问题很简单了撒一定是你那个 Type 字段不能为空值, 可是你插入的数据是 null, 所以报错您修改程序, 或者把错误的数据删掉就可以了 Oracle 甲方老总 : 报错的原因是什么? 为什么源数据库没行级锁事务有问题? 日志挖掘

4 SQL> select * from t_def; ID NAME 行级锁 TYPE a 日志挖掘 Oracle 事务这不科学!

5 SQL> select * from t_def where type is null; no rows selected SQL> select * from t_def where type is not null; ID NAME TYPE a SQL> select dump(type) from t_def; DUMP(TYPE) NULL 行级锁 Oracle 事务 SQL> select nvl(type, 'is null') from t_def; NVL(TYPE is null 日志挖掘

6 第一个查询和第二个查询明确的告诉我们,TYPE 列的值不是 NULL 值第三个查询和第四个查询又确切告之我们,TYPE 列是 NULL 值到底 TYPE 列的值是 NULL 还是 NOT NULL 呢? 这是一个问题!

7 查查数据到底是什么样的看块结构,dump dump 块

8 案例分析 SQL> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t_def; DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) SQL> alter system dump datafile 4 block 73109; System altered 行级锁 Oracle 事务日志挖掘

9 Block header dump: 0x0102a435 Object id on Block? Y seg/obj: 0x145df csc: 0x00.23b6097 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x102a430 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x b.00001afc 0x00c026de.11be.10 --U- 1 fsc 0x b6098 0x02 0x x fsc 0x bdba: 0x0102a435 data_block_dump,data header at 0x7fe7fdc97264 =============== block_row_dump: tab 0, row tl: 8 fb: --H-FL-- lb: 0x1 cc: 2 col 0: [ 2] c1 02 col 1: [ 1] 61 end_of_block_dump End dump data blocks tsn: 4 file#: 4 minblk maxblk

10 根据实际的存储得知,Type 列确实为空可是, 为什么用 select * from t_def where type is null 查看,Oracle 会告诉我们一个错误的结果呢?

11 SQL> set autot on exp SQL> select * from t_def where type is null; no rows selected Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time 行级锁 0 SELECT STATEMENT (0) * 1 FILTER 2 TABLE ACCESS FULL T_DEF (0) 00:00: Predicate Information (identified by operation id): filter(null IS NOT NULL) 日志挖掘 Oracle 事务

12 SQL> select * from t_def where type is not null; ID NAME TYPE a Execution Plan Plan hash value: Id Operation Name Rows Bytes Cost (%CPU) Time SELECT STATEMENT (0) 00:00:01 1 TABLE ACCESS FULL T_DEF (0) 00:00:

13 由于表中的 TYPE 列具有非空约束 (not null), 导致 CBO 给出的执行计划返回了错误的结果当限定条件为 :where type is null 时,CBO 认为此查询与约束相悖, 自动添加了一个 (null is not null) 的过滤, 返回零行结果

14 Why: 该列中会有空值? How: 数据怎么绕过非空约束的?

15 查询表结构 SQL> select dbms_metadata.get_ddl('table', 'T_DEF') from dual; DBMS_METADATA.GET_DDL('TABLE','T_DEF') CREATE TABLE "TEST"."T_DEF" ( "ID" NUMBER, "NAME" VARCHAR2(8) DEFAULT 'a', "TYPE" VARCHAR2(8) DEFAULT '' NOT NULL ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL NEXT MINEXTENTS 1 MAXEXTENTS PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"

16 SQL> CREATE TABLE T_TEST (ID NUMBER, NAME VARCHAR2(30) DEFAULT '' NOT NULL); 表已创建 SQL> INSERT INTO T_TEST (ID) VALUES (1); INSERT INTO T_TEST (ID) VALUES (1) * 第 1 行出现错误 : ORA-01400: 无法将 NULL 插入 ("TEST"."T_TEST"."NAME") 测试 : 如果列上有非空约束, 即使该列 default 是 null 值, 也是无法插入 null 值的

18 Null Not Null 约束 CBO 优化器 Oracle 11g 新特性 Default 快速添加非空默认值版本演进 10g---null is not null 执行计划 11g

19 快速添加非空默认值 SQL> create table t_def (id number, name varchar2(30) default '' not null); Table created. SQL> insert into t_def values (1, 'a'); 1 row created. SQL> alter table t_def add type varchar2(8) default '' not null; Table altered. SQL> select * from t_def; ID NAME TYPE a Oracle 确实允许 NOT NULL 列的默认值为 NULL, 如果不指定默认值那么就相当于默认值为 NULL, 但是对于 11g 新增的新特性而言,DEFAULT 为 NULL 是要禁止的, 否则就会导致现有记录的 NOT NULL 字段出现 NULL 值

20 恩墨学院网站 : 电话 :

PowerPoint Presentation

PowerPoint Presentation 数据库培训项目研究 Oracle 索引探究 B*tree 索引与位图索引的特点作者 : 赵超 2008 年 12 月 18 日实验环境 Windows-server2003 内存 :2G Oracle 10.2.0 ORACLE_SID=orcl 索引类型 B*tree 索引 ( 默认方式 ) 位图索引 (bitmap) 反