让IS NULL走起索引
来源:互联网 发布:mac和win7传输文件工具 编辑:程序博客网 时间:2024/06/12 01:24
由于业务的需要:查看某列值为NULL的记录有多少。这是一个十分简单的需求,同时也很容易实现。无非就是使用如下语句:
以下是一个模拟(附带着我的思路):
知道了情况就问呗,问如何解决这样的情况,最后得到了答案。创建一个伪列的索引就行了。
具体如下:
在接触到函数索引的时候也有提到以上类似的场景(查询NULL值的记录,NULL值的记录在表中不到10%)。就能使用函数索引来加快搜索。
SELECT * FROM table1 WHERE xx IS NULL;结果得出的结果只有几十条记录,但是执行的时间比我想象中的长了许多。一般这样的查询只是要秒秒钟的问题,但是却花了十几秒,而平时使用IS NOT NULL也没有那么慢啊。于是在有空的时候就查看原因。
以下是一个模拟(附带着我的思路):
构造测试表
DROP TABLE t PURGE;CREATE TABLE t(a NUMBER);CREATE INDEX idx_t ON t(a);INSERT INTO t VALUES(NULL);INSERT INTO t VALUES(NULL);INSERT INTO t VALUES(1);INSERT INTO t VALUES(1);INSERT INTO t VALUES(1);INSERT INTO t VALUES(1);INSERT INTO t VALUES(1);INSERT INTO t VALUES(1);INSERT INTO t VALUES(1);COMMIT;EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T');EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>USER, indname=>'IDX_T');遇到这样的问题我首先是看一下执行语句的相关的执行计划
SET NULL UNKOWN --设置如果只为NULL就以UNKOWN显示SET AUTOTRACE ON EXPLAINSELECT * FROM t WHERE a IS NULL; A----------UNKNOWNUNKNOWNExecution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 2 | 6 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A" IS NULL)SELECT * FROM t WHERE a IS NOT NULL; A---------- 1 1 1 1 1 1 17 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1512349780--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7 | 21 | 1 (0)| 00:00:01 ||* 1 | INDEX FULL SCAN | IDX_T | 7 | 21 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A" IS NOT NULL)从上的现象看出使用IS NULL时候没有使用索引,而使用IS NOT NULL有使用索引,这让我很郁闷。于是我顺道的就查看了一下IDX_T索引的所在的列。
如下:
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME = 'IDX_T';TABLE_NAME INDEX_NAME COLUMN_NAME------------------------------ ------------------------------ --------------------T IDX_T A很明显的展示了在表T的a列上是有索引的,这时我就在IS NULL和IS NOT NULL中迷茫了,不知所措啊。于是只能将问题放在一边了。直到一天我知道了DBMS_STATS.GATHER_INDEX_STATS()的作用的时候我又回想起了这个情况于是我有查看了一下有关索引的统计信息。
如下:
SELECT index_name, num_rows from user_indexes where index_name = 'IDX_T';INDEX_NAME NUM_ROWS------------------------------ ----------IDX_T 7从上面可以看出索引的统计信息的NUM_ROWS字段只有7行,而实际的数据总量是9行。按道理应该索引的NUM_ROWS应该是9才对缺少了2,很明显少了值为NULL的那2行。原来索引是不将NULL值的行记录在索引中的。在网上查了一下视乎有这种说法(本人不太确定只是猜测,毕竟统计信息的知识太多个人感觉好难,也容易混淆,难记)。
知道了情况就问呗,问如何解决这样的情况,最后得到了答案。创建一个伪列的索引就行了。
具体如下:
SET AUTOTRACE OFFDROP INDEX idx_t;CREATE INDEX idx_t ON t(a, 0);EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T');EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>USER, indname=>'IDX_T');SET AUTOTRACE ON EXPLAINSELECT * FROM t WHERE a IS NULL; A----------UNKNOWNUNKNOWNExecution Plan----------------------------------------------------------Plan hash value: 2296882198--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 6 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| IDX_T | 2 | 6 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("A" IS NULL)SELECT * FROM t WHERE a IS NOT NULL; A---------- 1 1 1 1 1 1 17 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1512349780--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 7 | 21 | 1 (0)| 00:00:01 ||* 1 | INDEX FULL SCAN | IDX_T | 7 | 21 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("A" IS NOT NULL)从上面可以上到IS NULL 走索引了,IS NOT NULL 也走索引
SELECT index_name, num_rows from user_indexes where index_name = 'IDX_T';INDEX_NAME NUM_ROWS------------------------------ ----------IDX_T 9索引的统计信息是9和表的行数是一样的。
在接触到函数索引的时候也有提到以上类似的场景(查询NULL值的记录,NULL值的记录在表中不到10%)。就能使用函数索引来加快搜索。
如下:
SET AUTOTRACE OFFDROP INDEX idx_t;CREATE IDNEX idx_t ON t(DECODE(a, NULL,0, NULL));EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'T');EXEC DBMS_STATS.GATHER_INDEX_STATS(ownname=>USER, indname=>'IDX_T');SET AUTOTRACE ON EXPLAINSELECT * FROM t WHERE DECODE(a, NULL, 0, NULL) = 0; A----------UNKNOWNUNKNOWNExecution Plan----------------------------------------------------------Plan hash value: 1594971208-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 8 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_T | 2 | | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access(DECODE(TO_CHAR("A"),NULL,0,NULL)=0)SELECT index_name, num_rows from user_indexes where index_name = 'IDX_T';INDEX_NAME NUM_ROWS------------------------------ ----------IDX_T 2可以看到以上结果走了索引,而且统计信息只有2条。在这里我做一下解释(是个人理解的,不对请指正)我们在创建索引的时候是使用DECODE(a, NULL, 0, NULL)。这个函数很好理解就是使用a的值和NULL比较,如果相等就变成0,而如果不相等就变成NULL。在表的记录中a的值只有2行为NULL,7行是有真实值,所以在创建索引时就生成2个0值,和7个NULL值。而根据前面所的oracle不会将NULL值行加入索引中,所以只能将为0的2行加入索引中了。所以索引统计信息的NUM_ROWS为2。
注意:要让oracle使用函数索引在谓词(WHERE)后要有相关函数。如上的SELECT查询。
0 0
- 让IS NULL走起索引
- Oracle is null不走索引问题
- 如何让table表的null列由不走索引变为可走索引
- 索引--is null
- 验证索引列NULL不走索引
- 转载-如何让oracle强制走索引
- “is Null”或“is not null”不能使用索引
- 讨论B树索引中的 is null/is not null
- is null 和 is not null 都不能利用 索引
- is null 条件使用索引的方法
- 通过复合索引来避免is null使索引失效!
- 8.2.1.8 IS NULL Optimization IS NULL 优化 (索引不存储NULL值)
- 测试 走起!!!
- Scala走起
- junit走起~~~
- ubuntu 走起
- NodeJS走起
- 新博客,走起!
- 第十二周项目1.4 十进制与二进制扯不开的关系
- 新手python入门之debug=True自动编译原理
- javabase简记--第四天
- Linux makefile中的= := ?=操作符
- Android Parcelable和Serializable的区别
- 让IS NULL走起索引
- 第十二周项目2 烧脑的汉诺塔
- c++学习笔记二
- 第十二周项目四:银行系统(一)
- 浅谈django 和 get post 方法
- VC6.0的MFC简单计算器制作
- [JAVA面试] java面试
- C6416CSL芯片支持库:第六节 芯片驱动DSP程序
- 关于使用Ajax传中文数据到后台接受乱码问题 data:{"dictype":"客户信誉度"}