Oracle数据库性能优化 -非空约束重要性全表扫描的性能提升

1.1测试现象CREATE TABLE T1 AS SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS;CREATE TABLE T2 AS SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS;CREATE INDEX IDX1 ON T1(OBJECT_ID);EXPLA...

执笔画情ora

732人浏览 · 2019-09-02 17:19:03

执笔画情ora · 2019-09-02 17:19:03 发布

1 测试现象

CREATE TABLE T1 AS SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS;

CREATE TABLE T2 AS SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS;

创建索引idx1

CREATE INDEX IDX1 ON T1(OBJECT_ID);

EXPLAIN PLAN FOR

SELECT T1.OBJECT_ID FROM T1 LEFT JOIN T2 ON T1.OBJECT_ID=T2.OBJECT_ID;

COMMIT;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 1823443478

-----------------------------------------------------------------------------------

| Id | Operation          | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      | 69236 | 1757K|       |   415   (1)| 00:00:01 |

|* 1 | HASH JOIN OUTER   |      | 69236 | 1757K| 1696K|   415   (1)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| T1   | 69236 |   878K|       |   124   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   | 69414 |   881K|       |   124   (0)| 00:00:01 |

-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"(+))

Note

----- - dynamic statistics used: dynamic sampling (level=2)

如果将列指定为非空

alter table t1 modify object_id not null;

alter table t1 add constraint ooooo check(object_id is not null);

insert into t2 select object_id,object_name from dba_objects where rownum<400

Plan hash value: 2754336310

---------------------------------------------------------------------------

| Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    26 |     3   (0)| 00:00:01 |

|* 1 | HASH JOIN OUTER   |      |     1 |    26 |     3   (0)| 00:00:01 |

|   2 |   INDEX FULL SCAN | IDX1 |     1 |    13 |     1   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   |   399 | 5187 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID"(+)

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

非空约束是字段的一个重要属性。但是，很多时候，数据库表的设计人员似乎并不十分在意这个属性。

最常见的现象就是，除了主键字段外，所有字段都不指定该属性。而在Oracle中，默认是允许为空。

而实际上，优化器在选择执行计划时，非空约束是一个重要的影响因素。

为了说明问题，我们建立以下测试表，然后分别说明非空约束在各种情况下对执行计划和性能的影响。
SQL代码

HELLODBA.COM>create table t_test1 tablespace DEMO as select * from dba_objects;

Table created.
HELLODBA.COM>alter table T_TEST1 add constraint T_TEST1_PK primary key (OBJECT_ID) using index tablespace DEMOTSINX;
Table altered.
HELLODBA.COM>update t_test1 set SUBOBJECT_NAME=OBJECT_NAME where SUBOBJECT_NAME is null;
32072 rows updated.
HELLODBA.COM>commit;
Commit complete.
HELLODBA.COM>desc t_test1
Name Null? Type
---------------------------------------------------------------------------------------------------------------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
LIO NUMBER

谓词评估 在上面表中，字段SUBOBJECT_NAME中不存在空值，但也没有非空约束，再看以下查询，查找该字段的空值记录：
SQL代码

HELLODBA.COM>select * from t_test1 where SUBOBJECT_NAME is null;

no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1883417357
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 45 (0)| 00:00:46 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 1 | 96 | 45 (0)| 00:00:46 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SUBOBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
665 consistent gets
0 physical reads
0 redo size
1048 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

我们看到，需要对表进行全表扫描（关于索引，随后再讨论）。而如果我们加上非空约束，可以看到执行计划已经性能的变化：
SQL代码

HELLODBA.COM>alter table t_test1 modify SUBOBJECT_NAME not null;

Table altered.
HELLODBA.COM>select * from t_test1 where SUBOBJECT_NAME is null;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4146611218
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 0 (0)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_TEST1 | 47585 | 4461K| 45 (0)| 00:00:46 |
------------------------------------------------------------------------------
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
1048 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

注意到，在全表扫描之前，增加了一个filter，而fileter的表达式是NULL IS NOT NULL，其逻辑结果是FALSE，因此，实际上其子操作（全表扫描）并未执行。相应的，请性能数据里面CR为0。
之所以优化器会为执行计划增加这样一个filter，是因为优化器在做查询转换（Query Transformation）时，会将非空约束作为参照条件之一，对where子句的谓词做逻辑结果评估，如果评估结果为false，则会增加一个这样的filter，以避免执行一些高代价的操作。从10053跟踪文件中，可以看到这对于优化器对执行计划代价估算的影响：
SQL代码

Cdn, Cost adjusted (to ~ 0) as where clause evalutes to FALSE

Final - All Rows Plan: Best join order: 1
Cost: 0.0000 Degree: 1 Card: 1.0000 Bytes: 4568160
Resc: 0.0000 Resc_io: 0.0000 Resc_cpu: 0
Resp: 0.0000 Resp_io: 0.0000 Resc_cpu: 0

非空约束对索引选择的影响 我们知道，Oracle中B*树索引中不存在空键值，即在表的数据记录中，如果索引中所有字段都为空，则该记录不会被构建到索引树中。也就是说，如果索引字段上没有非空约束，则表记录与索引记录不是完全映射的。
我们先去掉subobject_name上的非空约束，并在上面建立索引：
SQL代码

HELLODBA.COM>alter table t_test1 modify subobject_name null;

Table altered.
HELLODBA.COM>create index t_test1_subo_idx on t_test1(subobject_name) compute statistics;
Index created.

执行以下语句，以获取subobject_name最小的10条记录。为了提高效率，我们希望直接从索引中直接读取前10条ROWID（索引数据已经按照subobject_name排序），然后根据ROWID获取数据记录：
SQL代码

HELLODBA.COM>select owner,object_name,subobject_name from t_test1 t1, (select /*+index(t t_test1_subo_idx)*/rowid rid from t_test1 t where rownum<=10order by subobject_name) v where t1.rowid=v.rid;

OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ ------------------------------ ------------------------------
SYS ICOL$ BBB
SYS I_USER1 BBB
SYS CON$ BBB
SYS UNDO$ BBB
SYS I_PROXY_ROLE_DATA$_1 BBB
SYS I_OBJ# BBB
SYS PROXY_ROLE_DATA$ BBB
SYS I_IND1 BBB
SYS I_CDEF2 BBB
SYS C_COBJ# BBB
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4050478946
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 560 | | 308 (1)| 00:05:09 |
| 1 | NESTED LOOPS | | 10 | 560 | | 308 (1)| 00:05:09 |
| 2 | VIEW | | 10 | 120 | | 298 (1)| 00:04:59 |
| 3 | SORT ORDER BY | | 10 | 160 | 2248K| 298 (1)| 00:04:59 |
|* 4 | COUNT STOPKEY | | | | | | |
| 5 | TABLE ACCESS FULL | T_TEST1 | 47585 | 743K| | 45 (0)| 00:00:46 |
| 6 | TABLE ACCESS BY USER ROWID| T_TEST1 | 1 | 44 | | 1 (0)| 00:00:02 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
707 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

但是，查询计划和结果看，语句并没有按照设想的方式执行，得出的数据也不是我们需要的。其原因就在于，由于空值不被索引，优化器无法确认索引数据是否涵盖了所有数据记录，因而它没有选择指定索引。
我们把非空约束加上，执行计划和结果就符合我们的需求了。
SQL代码

HELLODBA.COM>alter table t_test1 modify subobject_name not null;

Table altered.
HELLODBA.COM>select owner,object_name,subobject_name from t_test1 t1, (select /*+index(t t_test1_subo_idx)*/rowid rid from t_test1 t where rownum<=10
order by subobject_name) v where t1.rowid=v.rid;
OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ ------------------------------ ------------------------------
DEMO NO A
DEMO NO A
DEMO NO A
SYS ICOL$ BBB
SYS I_USER1 BBB
SYS CON$ BBB
SYS UNDO$ BBB
SYS C_COBJ# BBB
SYS I_OBJ# BBB
SYS PROXY_ROLE_DATA$ BBB
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3198566056
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 560 | 71 (0)| 00:01:11 |
| 1 | NESTED LOOPS | | 10 | 560 | 71 (0)| 00:01:11 |
| 2 | VIEW | | 10 | 120 | 61 (0)| 00:01:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | INDEX FULL SCAN | T_TEST1_SUBO_IDX | 47585 | 743K| 61 (0)| 00:01:01 |
| 5 | TABLE ACCESS BY USER ROWID| T_TEST1 | 1 | 44 | 1 (0)| 00:00:02 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
681 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

非空约束对连接查询的影响 在进行数据关联时，数据集中关联字段是否存在空值也会影响优化器对执行计划的选择。我们再创建一张测试表。
SQL代码

HELLODBA.COM>create table t_test2 tablespace DEMO as select * from dba_tables;

Table created.
HELLODBA.COM>alter table T_TEST2 add constraint T_TEST2_PK primary key (OWNER,TABLE_NAME) using index tablespace DEMOTSINX;
Table altered.
HELLODBA.COM>desc t_test2
Name Null? Type
-------------- -------- -----------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
...

再将subobject_name的非空约束去掉。
SQL代码

HELLODBA.COM>alter table t_test1 modify subobject_name null;

Table altered.

我们通过以下语句查找t_test1中subobject_name不为table_name的数据：
SQL代码

HELLODBA.COM>select t1.owner, t1.object_name, t1.subobject_name from t_test1 t1 where subobject_name not in (select table_name from t_test2 t2);

45135 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3538907136
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2379 | 76128 | 51 (0)| 00:00:52 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_TEST1 | 47585 | 1487K| 45 (0)| 00:00:46 |
|* 3 | TABLE ACCESS FULL| T_TEST2 | 1 | 18 | 6 (0)| 00:00:07 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "T_TEST2" "T2" WHERE
LNNVL("TABLE_NAME"<>:B1)))
3 - filter(LNNVL("TABLE_NAME"<>:B1))
Statistics
----------------------------------------------------------
392 recursive calls
0 db block gets
2217674 consistent gets
0 physical reads
0 redo size
2329590 bytes sent via SQL*Net to client
33473 bytes received via SQL*Net from client
3010 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
45135 rows processed

可以看到，执行计划通过添加函数LNNVL和NOT EXISTS，对数据进行过滤得到结果，性能相当低。
注意：当逻辑表达是中的操作数可能为空时，LNNVL函数可以判断出该表达式的结果。
我们再把非空约束加上，

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

全家桶集齐！Qwen3.5四款小模型上线魔乐社区，附昇腾全套实践教程

魔乐社区

Pont - 搭建前后端之桥：高效、灵活的接口管理工具

Pont 是一款强大的数据服务层解决方案，它能够帮助开发者快速搭建前后端之间的桥梁，实现接口的高效管理和代码自动生成。无论是新手还是有经验的开发者，都能通过 Pont 轻松处理接口文档、生成类型安全的 API 代码，从而显著提升开发效率。[![Pont 工具标志](https://raw.gitcode.com/gh_mirrors/po/pont/raw/3f1b7d4bbba3fd2dda