【Oracle index】B*Tree索引之NULL统计技巧
2013-04-25 15:29:30
B*Tree索引不存储全为NULL的列(单列NULL,多列全为NULL),在查询中,如果需要统计信息或谓词需要查询NULL,就走不了索引,这时候,有一些技巧可以考虑。
构造数据如下:
1.COUNT统计NULL的数据
因为B*Tree不存储全为NULL的值,所以走了FULL TABLE SCAN
2.建立函数索引将NULL值纳入到索引中
建立函数索引之后的确可以,类似地可以使用NVL,DECODE,CASE WHEN等建立函数索引,但是函数索引有明显的缺点:
1)必须修改SQL语句和函数索引匹配
2)这种类似的函数索引,必须要求object_id不存在0的数据,这样有所限制,如果object_id存在0,那么会使统计错误
3.采用改进的函数索引,伪列组合索引
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)
分类: Oracle
构造数据如下:
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)