[20241120]NOT NULL约束与Check( xx is not null)约束.txt

[20241120]NOT NULL约束与Check( xx is not null)约束.txt --//作者迁移时提到2者不同，链接https://www.anbob.com/archives/8565.html，21c重复测试看看。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试环境建立： SCOTT@book01p> create table t1 as select * from all_objects; Table created. --//object_id字段存在约束。 SCOTT@book01p> @ desczz t1 OBJECT_ID SCOTT@book01p> @ pr ============================== DESC_OWNER                    : SCOTT DESC_TABLE_NAME               : T1 SAMPLE_SIZE                   : 69953 LAST_ANALYZED                 : 2024-11-20 16:56:08 DESC_COLUMN_ID                :    4 DESC_COLUMN_NAME              : OBJECT_ID DESC_NULLABLE                 : NOT NULL ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DESC_DATA_TYPE                : NUMBER(,) NUM_DISTINCT                  : 69953 DESC_DENSITY                  : .0000142953125670093 NUM_NULLS                     : 0 HISTOGRAM                     : NUM_BUCKETS                   : 1 TRANS_LOW                     : 2 TRANS_HIGH                    : 85370 PL/SQL procedure successfully completed. SCOTT@book01p> create table t2 as select * from t1; Table created. --//分析表略。 SCOTT@book01p> alter table scott.t2 modify(object_id  null); Table altered. SCOTT@book01p> alter table t2 add (constraint t2_ck_object_id check(object_id is not null)) ; Table altered. 3.测试： SCOTT@book01p> select * from t1 where object_id is null ; no rows selected SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  c89df20z2kbav, child number 1 ------------------------------------- select * from t1 where object_id is null Plan hash value: 3332582666 ----------------------------------------------------------------------------------------------------------- | Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | ----------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |      1 |        |       |     1 (100)|          |      0 |00:00:00.01 | |*  1 |  FILTER            |      |      1 |        |       |            |          |      0 |00:00:00.01 | |   2 |   TABLE ACCESS FULL| T1   |      0 |  69953 |  9837K|   413   (1)| 00:00:01 |      0 |00:00:00.01 | ----------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / "T1"@"SEL$1" Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(NULL IS NOT NULL) 25 rows selected. --//虽然全部扫描，注意filter条件是(NULL IS NOT NULL)，所以 A-Rows 为0，根本没有扫描表。 SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8fxj4av0mw8t1, child number 0 ------------------------------------- select * from t2 where object_id is null Plan hash value: 1513984157 -------------------------------------------------------------------------------------------------------------------- | Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | -------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |      1 |        |       |   411 (100)|          |      0 |00:00:00.01 |    1480 | |*  1 |  TABLE ACCESS FULL| T2   |      1 |      1 |   144 |   411   (1)| 00:00:01 |      0 |00:00:00.01 |    1480 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / "T2"@"SEL$1" Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("OBJECT_ID" IS NULL) --//虽然约束constraint t2_ck_object_id check(object_id is not null)存在并且使能，但是oracle的执行计划选择全表扫描，注意 --//看Buffers列。只能说明oracle还不够智能，知道约束的存在并没有做出很好的判断。 3.实际上一些约束对于控制执行计划还是有用的，通过例子说明： SCOTT@book01p> update  t2 set object_name=upper(object_name) where upper(object_name)<>object_name; 44584 rows updated. SCOTT@book01p> commit ; Commit complete. --//修改object_name全部大写。 SCOTT@book01p> alter table scott.t2 add constraint ck_object_name  check (upper(object_name)=object_name) enable validate; Table altered. SCOTT@book01p> create index i_t2_object_name on t2(object_name); Index created. SCOTT@book01p> select * from t2 where upper(object_name)='A'; no rows selected SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  bz0fg2r8960b5, child number 0 ------------------------------------- select * from t2 where upper(object_name)='A' Plan hash value: 2535364348 ----------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                           | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | ----------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                    |                  |      1 |        |       |     4 (100)|          |      0 |00:00:00.01 |       3 |      2 | |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T2               |      1 |    700 |    98K|     4   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |      2 | |*  2 |   INDEX RANGE SCAN                  | I_T2_OBJECT_NAME |      1 |      1 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       3 |      2 | ----------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / "T2"@"SEL$1"    2 - SEL$1 / "T2"@"SEL$1" Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("OBJECT_NAME"='A')        filter(UPPER("OBJECT_NAME")='A') 26 rows selected. --//虽然查询条件是upper(object_name)='A';，而我建立的索引并不是函数索引。oracle依旧可以使用普通索引完成查询工作。