[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依旧可以使用普通索引完成查询工作。