null value index

最新推荐文章于 2025-05-30 09:41:00 发布

loryliu

最新推荐文章于 2025-05-30 09:41:00 发布

阅读量494

点赞数

分类专栏： oracle 文章标签： null value index exa

oracle 专栏收录该内容

203 篇文章

订阅专栏

本文详细探讨了在SQL中处理NULL值时的索引使用情况及优化策略，通过创建不同类型的索引来提升NULL值查询效率，并分析了执行计划的变化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

*** Create a table based on dba_tables
SQL> CREATE TABLE test_nulls AS SELECT * FROM dba_tables;
Table created.
*** Note the PCT_FREE column conatins 50 null values
SQL> SELECT COUNT(*) FROM test_nulls WHERE PCT_FREE IS NULL;
COUNT(*)
----------
50
*** create an index on the PCT_FREE column
SQL> CREATE INDEX test_nulls_i ON test_nulls(pct_free);
Index created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'BOWIE', tabname=>'TEST_NULLS', estimate_percent=>NULL,
cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
*** Perform a search for all NULL values
SQL> SELECT * FROM test_nulls WHERE pct_free IS NULL;
50 rows selected.
Execution Plan
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time
|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 10250 | 12 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | TEST_NULLS | 50 | 10250 | 12 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------
*** Note the index is ignored
SQL> SELECT /*+ INDEX(tn, test_nulls_i) */ * FROM test_nulls tn WHERE pct_free IS NULL;
50 rows selected.
Execution Plan
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time
|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 10250 | 12 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | TEST_NULLS | 50 | 10250 | 12 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------
*** Hints do no good. A block dump on the index shows that the NULL values are not indexed
*** However, create a concatenated index with the OWNER column, which has a NOT NULL constraint ...
SQL> CREATE INDEX test_nulls_i2 ON test_nulls(pct_free, owner) COMPUTE STATISTICS;
Index created.
SQL> SELECT * FROM test_nulls WHERE pct_free IS NULL;
50 rows selected.
Execution Plan
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 10250 | 6 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_NULLS | 50 | 10250 | 6 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_NULLS_I2 | 50 | | 2 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------
*** And we see the index is now used

*** However, create another index with just a space ' ' tagged

这里再补充一个， CREATE INDEX test_nulls_i2 ON test_nulls(,owner,pct_free);

SELECT COUNT(*) FROM test_nulls WHERE PCT_FREE IS NULL;

默认是不会走这个Index：

SQL> SELECT * FROM sajet.test_nulls tn WHERE pct_free IS NULL;
50 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4225836326
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 10500 | 18 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_NULLS | 50 | 10500 | 18 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PCT_FREE" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
76 consistent gets
0 physical reads
0 redo size
6593 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed

可以强制走index，消耗的资源会比FULL Table小：

SQL> SELECT /*+ INDEX(tn, test_null_i2) */ * FROM sajet.test_nulls tn WHERE pct_free IS NULL
50 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1164047204

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

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

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

| 0 | SELECT STATEMENT | | 50 | 10500 | 12 (0)
| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST_NULLS | 50 | 10500 | 12 (0)
| 00:00:01 |

|* 2 | INDEX FULL SCAN | TEST_NULL_I2 | 50 | | 8 (0)
| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PCT_FREE" IS NULL)
filter("PCT_FREE" IS NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
6582 bytes sent via SQL*Net to client
525 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed