Alert: not null Defining Integrity Constraints or Check( xx is not null) Constraint 性能差异
最近在将Oracle迁移到基于PostgreSQL的国产数据库时,遇到了一个现象。为了确保数据迁移顺利进行,迁移工具将Oracle表中定义的NOT NULL
约束更改为外部的CHECK NOT NULL
约束。这样做的目的是在建表和导入数据完成后,再添加CHECK
约束。尽管功能上这两者几乎相同,但在性能上还是存在一些差异。这里做一个简单的记录。
具体步骤如下:
-
- 建表时暂不定义
NOT NULL
约束: - 导入数据:
- 导入数据完成后,增加
CHECK
约束:ALTER TABLE example_table ADD CONSTRAINT check_not_null CHECK (name IS NOT NULL);-- 当然增加约束理可以使用enable novalidate不验证已存在数据提速DDL完成
- 建表时暂不定义
Oracle在官方文档上有一些信息,请访问:Choosing Between CHECK and NOT NULL Integrity Constraints
Therefore, integrity constraints for a single column can, in practice, be written in two forms: using the constraint or a constraint. For ease of use, you should always choose to define integrity constraints, instead of constraints with the condition CHECK ( NOT NULL)
下面我演示一下oracle和PostgreSQL对于这两种约束的区别。
test1表是表定义性质的not null; test2是表外独立的constraint。
Oracle 11.2.0.4
构造测试数据
SQL> create table anbob.test1(id int not null, name varchar2(4000), mark varchar2(3000)); Table created. SQL> insert into anbob.test1 select rownum,lpad(rownum,3900,'x'),lpad(rownum,2900,'y') from dual connect by rownum<=10000; 10000 rows created. SQL> commit; Commit complete. SQL> BEGIN dbms_stats.gather_table_stats('ANBOB','test1',null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END; 2 / PL/SQL procedure successfully completed. SQL> create table anbob.test2(id int,name varchar2(4000),imark varchar2(3000)); Table created. SQL> insert into anbob.test2 select * from anbob.test1; 10000 rows created. SQL> commit; Commit complete. SQL> alter table anbob.test2 add (CONSTRAINT T_CK_NN CHECK(ID IS NOT NULL)) ; Table altered. SQL> BEGIN dbms_stats.gather_table_stats('anbob','test2', null, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END; 2 / PL/SQL procedure successfully completed. QL> @tab anbob.test Show tables matching condition "%anbob.test%" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS -------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ------------------- ---------------------------------------- -------- ANBOB TEST2 TAB 10000 10097 0 0 6806 2024-11-16 16:13:32 1 DISABLED ANBOB TEST1 TAB 10000 10143 0 0 6806 2024-11-16 16:19:30 1 DISABLED SQL> desc anbob.test1; Name Null? Type ------------------------------- -------- ---------------------------- 1 ID NOT NULL NUMBER(38) 2 NAME VARCHAR2(4000) 3 MARK VARCHAR2(3000) SQL> desc anbob.test2 Name Null? Type ------------------------------- -------- ---------------------------- 1 ID NUMBER(38) 2 NAME VARCHAR2(4000) 3 IMARK VARCHAR2(3000) SQL> @cons anbob.test1 Show constraints on table anbob.test1... OWNER TABLE_NAME CONSTRAINT_NAME C R_CONSTRAINT_NAME COLUMN_NAME POSITION STATUS VALIDATED ------------------------------ ------------------------------ ------------------------------ - ------------------------------ ------------------------------ ---------- -------- ------------- ANBOB TEST1 SYS_C0011251 C ID ENABLED VALIDATED SQL> @cons anbob.test2 Show constraints on table anbob.test2... OWNER TABLE_NAME CONSTRAINT_NAME C R_CONSTRAINT_NAME COLUMN_NAME POSITION STATUS VALIDATED ------------------------------ ------------------------------ ------------------------------ - ------------------------------ ------------------------------ ---------- -------- ------------- ANBOB TEST2 T_CK_NN C ID ENABLED VALIDATED
对比两个个执行计划
SQL> select * from anbob.test1 where id is null; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1588389598 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6806 | 0 (0)| | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| TEST1 | 10000 | 64M| 2751 (1)| 00:00:34 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NULL IS NOT NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 462 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
SQL> select * from anbob.test2 where id is null; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 300966803 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6806 | 2738 (1)| 00:00:33 | |* 1 | TABLE ACCESS FULL| TEST2 | 1 | 6806 | 2738 (1)| 00:00:33 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID" IS NULL) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 10107 consistent gets 0 physical reads 0 redo size 463 bytes sent via SQL*Net to client 509 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
PostgreSQL
构建测试数据
postgres=# create table anbob.test1(id int not null, name varchar(4000), mark varchar(3000)); postgres=#insert into anbob.test1 select x,lpad(x::text,3900,'x'),lpad(x::text,2900,'y') from generate_series(1,10000) as x; postgres=#create table anbob.test2(id int,name varchar(4000),imark varchar(3000)); postgres=# alter table anbob.test2 add CONSTRAINT test2_id_notnull CHECK( not(id is null or id='') ) ; 错误: 无效的类型 integer 输入语法: "" postgres=# alter table anbob.test2 add CONSTRAINT test2_id_notnull CHECK( not(id is null ) ) ; ALTER TABLE
为什么要带=”
因为在postgesql中”不为NULL,与oracle不同.
postgres=# alter table anbob.test2 add CONSTRAINT test2_name_notnull CHECK( not(name is null ) ) ;
ALTER TABLE
postgres=# insert into anbob.test2(name) values('');
错误: 关系 "test2" 的新列违反了检查约束 "test2_id_notnull"
描述: 失败, 行包含(null, , null).
postgres=# insert into anbob.test2(id,name) values(-1,'');
INSERT 0 1
postgres=# alter table anbob.test2 drop constraint test2_name_notnull;
ALTER TABLE
postgres=# alter table anbob.test2 add CONSTRAINT test2_name_notnull CHECK( not(name is null or name ='' ) ) ;
错误: check constraint "test2_name_notnull" of relation "test2" is violated by some row
postgres=# delete anbob.test2 where id=-1;
错误: 语法错误 在 "anbob" 或附近的
第1行delete anbob.test2 where id=-1;
^
postgres=# delete from anbob.test2 where id=-1;
DELETE 1
postgres=# alter table anbob.test2 add CONSTRAINT test2_name_notnull CHECK( not(name is null or name ='' ) ) ;
ALTER TABLE
postgres=# insert into anbob.test2(id,name) values(-1,'');
错误: 关系 "test2" 的新列违反了检查约束 "test2_name_notnull"
描述: 失败, 行包含(-1, , null).
测试两个执行计划的代价
postgres=# explain select * from anbob.test1 where id is null; QUERY PLAN ------------------------------------------ Result (cost=0.00..0.00 rows=0 width=0) One-Time Filter: false (2 行记录) postgres=# explain select * from anbob.test2 where id is null; QUERY PLAN --------------------------------------------------------- Seq Scan on test2 (cost=0.00..281.00 rows=1 width=111) Filter: (id IS NULL) (2 行记录)
小结:
对于列not null约束,永远应该使用表定义的not null,而不是独立的外部constraint对象。
还有更多异构数据迁移问题隐藏在项目中,如果您有国产化改造咨询需求,可以考虑联系我们(www.anbob.com首页的联系方式)
目前这篇文章还没有评论(Rss)