【Oracle index】B*Tree索引之NULL统计技巧

最新推荐文章于 2022-04-27 07:50:00 发布

转载最新推荐文章于 2022-04-27 07:50:00 发布 · 735 阅读

本文详细介绍了在Oracle数据库中利用B*Tree索引处理全为NULL的列时的策略，包括NULL数据的统计方法、通过函数索引和改进的函数索引实现NULL值的纳入，以及如何通过组合索引屏蔽NULL列，同时提供了执行计划的对比分析。

【Oracle index】B*Tree索引之NULL统计技巧 2013-04-25 15:29:30

分类： Oracle

B*Tree索引不存储全为NULL的列(单列NULL，多列全为NULL），在查询中，如果需要统计信息或谓词需要查询NULL，就走不了索引，这时候，有一些技巧可以考虑。

构造数据如下：

SQL> DROP TABLE t;
表已删除。
SQL> CREATE TABLE t AS SELECT * FROM dba_objects;
表已创建。
SQL> INSERT INTO t SELECT * FROM t;
已创建73035行。
SQL> commit;
提交完成。
SQL> SELECT COUNT(*) FROM t;
COUNT(*)
----------
146070

--置1000条object_id为NULL
SQL> UPDATE t SET object_id = NULL WHERE ROWNUM<1000;
已更新999行。
SQL> CREATE INDEX idx_t ON t(object_id);
索引已创建。

SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',cascade=>true);
PL/SQL 过程已成功完成。

1.COUNT统计NULL的数据

SQL> set autotrace traceonly exp
SQL> SELECT COUNT(*) FROM t WHERE object_id IS NULL;
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 588 (1)| 00:00:08 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T | 1023 | 5115 | 588 (1)| 00:00:08 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NULL)

因为B*Tree不存储全为NULL的值，所以走了FULL TABLE SCAN

2.建立函数索引将NULL值纳入到索引中

SQL> drop index idx_t;
索引已删除。
SQL> create index idx_t on t(nvl(object_id,0));
索引已创建。
SQL> SELECT COUNT(*) FROM t WHERE nvl(object_id,0)=0;
执行计划
----------------------------------------------------------
Plan hash value: 1500240790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| IDX_T | 1461 | 18993 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL("OBJECT_ID",0)=0)

建立函数索引之后的确可以，类似地可以使用NVL，DECODE,CASE WHEN等建立函数索引，但是函数索引有明显的缺点：
1）必须修改SQL语句和函数索引匹配
2）这种类似的函数索引，必须要求object_id不存在0的数据，这样有所限制，如果object_id存在0，那么会使统计错误

3.采用改进的函数索引，伪列组合索引

SQL> drop index idx_t;
索引已删除。
SQL> create index idx_t on t(object_id,0);
索引已创建。
SQL> SELECT COUNT(*) FROM t WHERE object_id is null;
执行计划
----------------------------------------------------------
Plan hash value: 1500240790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | INDEX RANGE SCAN| IDX_T | 1023 | 5115 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID" IS NULL)

t(object_id,0)是一个特殊的函数索引，使用object_id作为前导列，它有明显的优点，可以不管object_id的值，也不用改SQL 。

3.建立组合索引，屏蔽有NULL的列

SQL> drop index idx_t;
索引已删除。
SQL> create index idx_t on t(object_id,object_name);
索引已创建。
SQL> SELECT COUNT(*) FROM t WHERE object_id is null;
执行计划
----------------------------------------------------------
Plan hash value: 1500240790
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| * 2 | INDEX RANGE SCAN| IDX_T | 1023 | 5115 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID" IS NULL)