从 NULL 值看索引失效与 TABLE ACCESS FULL 现象

本期译文

翻译自：

https://www.howtosop.com/when-null-results-table-access-full/

正文开始

一、性能问题

在某些情况下，NULL 值会阻止语句使用列上的索引，这会导致全表扫描，在循环中严重影响性能。

让我们看看使用 index 和 full table scan 进行比较的情况。

（一）使用索引的情况（NOT NULL）

首先，我们想要统计 MANAGER_ID 不为 NULL 的数量。

SQL> select count(*) cnt from hr.employees where manager_id is not null;
        CNT
----------
        106

然后查看执行计划。

SQL> set linesize 85;
SQL> set pagesize 1000;
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  f67nzh2nk7pu0, child number 1
-------------------------------------
select count(*) cnt from hr.employees where manager_id is not null
Plan hash value: 3393209858
-----------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time        |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |       |       |     1 (100)|            |
|   1 |  SORT AGGREGATE  |                |     1 |     4 |            |            |
|*  2 |   INDEX FULL SCAN| EMP_MANAGER_IX |   106 |   424 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    2 - filter("MANAGER_ID" IS NOT NULL)
19 rows selected.

可以看到，语句使用了单列索引 EMP_MANAGER_IX，这还不错。

（二）全表扫描的情况（IS NULL）

另一方面，当我们想要统计 NULL 值时情况就不同了。

SQL> select count(*) cnt from hr.employees where manager_id is null;
        CNT
----------
         1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  bp859wff0gpsp, child number 1
-------------------------------------
select count(*) cnt from hr.employees where manager_id is null
Plan hash value: 1756381138
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time        |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |       |       |     3 (100)|            |
|   1 |  SORT AGGREGATE    |           |     1 |     4 |            |            |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |     1 |     4 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    2 - filter("MANAGER_ID" IS NULL)
19 rows selected.

这次是 TABLE ACCESS FULL。TABLE ACCESS FULL 意味着执行全表扫描而不是索引扫描，当表非常大时可能会耗费很多资源。

二、原因

这是因为单列索引 EMP_MANAGER_IX 不包含 MANAGER_ID 的 NULL 值，所以它无法知道哪个字段是 NULL。结果就是执行全表扫描。

这就是为什么索引的 NUM_ROWS 有时比其依赖的表的行数要小。

SQL> select num_rows from all_indexes where owner = 'HR' and index_name = 'EMP_MANAGER_IX';
  NUM_ROWS
----------
      106
SQL> select num_rows from all_tables where owner = 'HR' and table_name = 'EMPLOYEES';
  NUM_ROWS
----------
      107

差异是真实存在的。

三、解决方案

为了解决这个问题，我们需要一个复合索引，它能够包含 MANAGER_ID 的 NULL 值。

SQL> create index hr.emp_mgr_id_x on hr.employees (manager_id, 1);
Index created.

由于复合索引的第二个位置完全不重要，我们提供一个虚拟列作为占位。

让我们看看新的执行计划。

SQL> select count(*) cnt from hr.employees where manager_id is null;
        CNT
----------
         1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  bp859wff0gpsp, child number 1
-------------------------------------
select count(*) cnt from hr.employees where manager_id is null
Plan hash value: 2770830432
----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time        |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     1 (100)|            |
|   1 |  SORT AGGREGATE   |               |     1 |     4 |            |            |
|*  2 |   INDEX RANGE SCAN| EMP_MGR_ID_X |     1 |     4 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    2 - access("MANAGER_ID" IS NULL)
19 rows selected.

虽然 SQL_ID 相同，但 PLAN_HASH_VALUE 已经改变，因为执行计划中使用了复合索引。

让我们总结一下索引的转变：单列索引 -> 添加虚拟列 -> 复合索引。完成！