解析：
索引中不保存索引列全为空的记录，所以，仅仅在C1列上创建索引是没有作用的。创建由（C1,‘1’)构建的复合索引，由于存在常量‘1’，所以，无论C1列的值是否为空，其均可出现在索引中，并且该索引会被该SQL所使用。另外几种方法，虽然也可以达到类似避免全表扫的，使用相对高效的索引的目的，但要么需要改写SQL，要么需要修改表中的记录值，要么索引的体积较大，且还存在其它列在其中，当该其它列更新时，该索引也需要同步维护。代价均较“创建由（C1,‘1’)构建的复合索引”的方法更大。
以下测试验证一下字段有空值的时候，是否字段+常量的方式可优化？
测试表100万记录，其中有100条空值。
在创建C1,'0’复合索引后，确认可以走索引，速度提升5-7倍。
注意查看实际的执行计划，使用SQL：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
测试操作记录如下：
SQL> create table T1 as
2 select rownum as id,
3 to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
4 trunc(dbms_random.value(0, 100)) as C1,
5 dbms_random.string('x', 20) random_string
6 from dual
7 connect by level <= 1000000;
Table created.
Elapsed: 00:01:56.77
SQL> DECLARE
2 v_count NUMBER := 0;
3 BEGIN
4 FOR rec IN (
5 SELECT ROWID
6 FROM t1
7 ORDER BY DBMS_RANDOM.VALUE
8 ) LOOP
9 UPDATE t1
10 SET c1 = NULL
11 WHERE ROWID = rec.ROWID;
12
13 v_count := v_count + 1;
14
15 EXIT WHEN v_count >= 100;
16 END LOOP;
17 END;
18 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.41
SQL> select count(*) from t1 where c1 is null;
COUNT(*)
----------
100
Elapsed: 00:00:00.08
SQL> select * from t1 where rownum<10;
ID INC_DATETIME C1
---------- ------------------- ----------
RANDOM_STRING
--------------------------------------------------------------------------------
1 2024-07-04 08:17:37 1
95IFQRASHI3RTSZ7D2RN
2 2024-07-04 08:17:38 9
L4IQELKS962M75KDZG3G
3 2024-07-04 08:17:39 49
QSPYCV9MFHO6JZJ4ZI1G
ID INC_DATETIME C1
---------- ------------------- ----------
RANDOM_STRING
--------------------------------------------------------------------------------
4 2024-07-04 08:17:40 84
4P9B7RFBTJHFSKHEZ012
5 2024-07-04 08:17:41 48
GP26B53FGA85R1ZGENOT
6 2024-07-04 08:17:42 9
VA8NXZK510H6WA1FEM7Q
ID INC_DATETIME C1
---------- ------------------- ----------
RANDOM_STRING
--------------------------------------------------------------------------------
7 2024-07-04 08:17:43 54
ZBJF9Y42S3XCQ4M1QH73
8 2024-07-04 08:17:44 90
M7I76GLO47N4MBJ7S0SO
9 2024-07-04 08:17:45 7
EQLTY1JMHJUX16IZ1X3C
9 rows selected.
Elapsed: 00:00:00.00
SQL> explain plan for SELECT count(*) from t1 WHERE c1 IS NULL;
Explained.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2064 (1)| 00:00:25 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T1 | 84 | 1092 | 2064 (1)| 00:00:25 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("C1" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.
Elapsed: 00:00:00.02
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;
COUNT(*)
----------
100
Elapsed: 00:00:00.18
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g8a861jv5d0bj, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 IS NULL
Plan hash value: 3724264953
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T1 | 84 |
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.03
SQL> create index idx_t1 on t1(c1,'1');
Index created.
Elapsed: 00:00:03.94
SQL> explain plan for SELECT count(*) from t1 WHERE c1 IS NULL;
Explained.
Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1970818898
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 117 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1 | 84 | 1092 | 117 (0)| 00:00:02 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
2 - access("C1" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.
Elapsed: 00:00:00.02
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;
COUNT(*)
----------
100
Elapsed: 00:00:00.01
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g8a861jv5d0bj, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 IS NULL
Plan hash value: 1970818898
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 84 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1" IS NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.03
SQL>
SQL> SELECT count(*) from t1 WHERE c1='10';
COUNT(*)
----------
10214
Elapsed: 00:00:00.02
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID b9xy1g3vnm0mn, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1='10'
Plan hash value: 1970818898
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 9638 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=10)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.04
SQL> SELECT count(*) from t1 WHERE c1='0';
COUNT(*)
----------
9938
Elapsed: 00:00:00.01
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cjrzz90vx05mg, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1='0'
Plan hash value: 1970818898
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 9759 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=0)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.04
SQL> SELECT count(*) from t1 WHERE c1>'800000';
COUNT(*)
----------
0
Elapsed: 00:00:00.01
SQL> SELECT count(*) from t1 WHERE c1 like '90';
COUNT(*)
----------
10003
Elapsed: 00:00:00.39
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID d39ayt5dqmsgq, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 like '90'
Plan hash value: 2101382132
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 10481 |
-------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_CHAR("C1")='90')
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.04
SQL> SELECT count(*) from t1 WHERE c1 like '9%';
COUNT(*)
----------
110235
Elapsed: 00:00:00.48
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7ms2y2z4wnsh5, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 like '9%'
Plan hash value: 2101382132
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 112K|
-------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_CHAR("C1") LIKE '9%')
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.05
SQL> SELECT count(*) from t1 WHERE c1 >98;
COUNT(*)
----------
9872
Elapsed: 00:00:00.00
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 3vkuagjrjbgpm, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 >98
Plan hash value: 1970818898
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 10843 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1">98 AND "C1" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.04
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
INC_DATETIME VARCHAR2(19)
C1 NUMBER
RANDOM_STRING VARCHAR2(4000)
SQL> SELECT count(*) from t1 WHERE c1 >9;
COUNT(*)
----------
900067
Elapsed: 00:00:00.11
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cddkhnb16jq56, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 >9
Plan hash value: 2101382132
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 887K|
-------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">9)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.05
SQL> SELECT count(*) from t1 WHERE c1 >9;
COUNT(*)
----------
900067
Elapsed: 00:00:00.10
SQL> drop index idx_t1;
Index dropped.
Elapsed: 00:00:00.05
SQL> SELECT count(*) from t1 WHERE c1 >9;
COUNT(*)
----------
900067
Elapsed: 00:00:00.08
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cddkhnb16jq56, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 >9
Plan hash value: 3724264953
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T1 | 887K|
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">9)
Note
-----
- dynamic sampling used for this statement (level=2)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l
26 rows selected.
Elapsed: 00:00:00.03
SQL> SELECT count(*) from t1 WHERE c1 >9;
COUNT(*)
----------
900067
Elapsed: 00:00:00.09
SQL>
最后修改时间：2024-07-04 09:42:21
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
