索引里的NULL值与排序小记

最新推荐文章于 2022-10-02 11:32:20 发布

转载最新推荐文章于 2022-10-02 11:32:20 发布 · 84 阅读

本文通过创建表wxh_tbd1并建立索引w_i，演示了在Oracle中当WHERE子句包含NULL值时，查询计划如何发生变化。特别是对于NULL值的比较导致了额外的排序操作。

apollo@CRMG>create table wxh_tbd1 ( a varchar2(100),b varchar2(100) ,c varchar2(200));

Table created.

apollo@CRMG>insert into wxh_tbd1 select object_type ,status ,NAMESPACE from dba_objects;

apollo@CRMG>create index w_i on wxh_tbd1(a,b,c);

Index created.

apollo@CRMG>exec dbms_stats.gather_table_stats(user,'wxh_tbd1');

PL/SQL procedure successfully completed.

apollo@CRMG>select * from wxh_tbd1 where a='TABLE' AND B IS NULL AND C='c' order by c;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1269858568

-------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    15 |     1   (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| W_I |     1 |    15 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

apollo@CRMG>select * from wxh_tbd1 where a='TABLE' AND B IS NULL AND C>'c' order by c;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1380261624

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    15 |     2 (50)| 00:00:01 |
|   1 | SORT ORDER BY    |      |     1 |    15 |     2 (50)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN| W_I |     1 |    15 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

第二种情况出现了排序。原因是ORACLE傻B的不知道（NULL,1) 和(NULL,2）谁大谁小，因此第二种查询就会出现排序，DUMP出索引，索引里也是乱序的

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-694507/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-694507/