首页 » ORACLE 9i-23ai, PostgreSQL/GaussDB » Alert: not null Defining Integrity Constraints or Check( xx is not null) Constraint 性能差异

Alert: not null Defining Integrity Constraints or Check( xx is not null) Constraint 性能差异

最近在将Oracle迁移到基于PostgreSQL的国产数据库时，遇到了一个现象。为了确保数据迁移顺利进行，迁移工具将Oracle表中定义的NOT NULL约束更改为外部的CHECK NOT NULL约束。这样做的目的是在建表和导入数据完成后，再添加CHECK约束。尽管功能上这两者几乎相同，但在性能上还是存在一些差异。这里做一个简单的记录。

具体步骤如下：

    1. 建表时暂不定义 NOT NULL 约束：
    2. 导入数据：
    3. 导入数据完成后，增加 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> 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)

我要评论

上一篇：

下一篇：