首页 » ORACLE » where is null 走索引

where is null 走索引

anbob@ANBOB> col table_name for a20
anbob@ANBOB> select table_name,num_rows from user_tables;

TABLE_NAME             NUM_ROWS
-------------------- ----------
TESTCOPY                      4
TESTREG                       4
TESTCONN                      4
TESTGROUP                     2
TESTVIEW                      1
T6                            2
TEST_CONCAT                   5
TESTORDER                     1
ALLOBJ                    40698
TESTMAP                   10000
TESTCHR
TESTCONS                     10
CHAINED_ROWS                  1

13 rows selected.


anbob@ANBOB> select * from testmap where rownum<10;
       ID1        ID2
---------- ----------
      1693          2
      1694          2
      1695          2
      1696          2
      1697          2
      1698          2
      1699          2
      1700          2
      1701          2

9 rows selected.

anbob@ANBOB> update testmap set id1=null where rownum<100;

99 rows updated.

anbob@ANBOB> commit;

Commit complete.

看下面的执行计划
anbob@ANBOB> select * from testmap where id1 is null;
99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 760294235

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     7 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTMAP |     1 |     7 |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter("ID1" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       1743  bytes sent via SQL*Net to client
        451  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

anbob@ANBOB> create index idx_testmap_id1 on testmap(id1);
Index created.

anbob@ANBOB> select * from testmap a where id1<700;
699 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 924736260

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |   700 |  4900 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTMAP         |   700 |  4900 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TESTMAP_ID1 |   700 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access("ID1"<700)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         99  consistent gets
          0  physical reads
          0  redo size
      15346  bytes sent via SQL*Net to client
        891  bytes received via SQL*Net from client
         48  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        699  rows processed



anbob@ANBOB> select * from testmap where id1 is null;

99 rows selected.

anbob@ANBOB> /

99 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 760294235

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     7 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTMAP |     1 |     7 |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter("ID1" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       1743  bytes sent via SQL*Net to client
        451  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed
         
anbob@ANBOB> select /*+index(a idx_testmap_id1) */* from testmap a where id1 is null;

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 760294235

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |     7 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TESTMAP |     1 |     7 |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter("ID1" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       1743  bytes sent via SQL*Net to client
        451  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed
         
anbob@ANBOB> create index idx_testmap_id1null on testmap(id1,'0');
Index created.

anbob@ANBOB> select * from testmap  where id1 is null;

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3130605591

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TESTMAP             |     1 |     7 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TESTMAP_ID1NULL |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   2 - access("ID1" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         17  consistent gets
          1  physical reads
          0  redo size
       1743  bytes sent via SQL*Net to client
        451  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed
         
看一下索引的高度
anbob@ANBOB> select object_id from user_objects where object_name='IDX_TESTMAP_ID1NULL';
system@ANBOB> alter session set events 'immediate trace name treedump level :object_id';

Session altered.

查看trace 内容
----- begin tree dump
branch: 0x1800234 25166388 (0: nrow: 24, level: 1)
   leaf: 0x1800235 25166389 (-1: nrow: 428 rrow: 428)
   leaf: 0x1800236 25166390 (0: nrow: 422 rrow: 422)
   leaf: 0x1800237 25166391 (1: nrow: 422 rrow: 422)
   leaf: 0x1800238 25166392 (2: nrow: 422 rrow: 422)
   leaf: 0x1800239 25166393 (3: nrow: 422 rrow: 422)
   leaf: 0x180023a 25166394 (4: nrow: 422 rrow: 422)
   leaf: 0x180023b 25166395 (5: nrow: 422 rrow: 422)
   leaf: 0x180023c 25166396 (6: nrow: 422 rrow: 422)
   leaf: 0x180023d 25166397 (7: nrow: 422 rrow: 422)
   leaf: 0x180023e 25166398 (8: nrow: 422 rrow: 422)
   leaf: 0x180023f 25166399 (9: nrow: 422 rrow: 422)
   leaf: 0x1800240 25166400 (10: nrow: 422 rrow: 422)
   leaf: 0x1800242 25166402 (11: nrow: 422 rrow: 422)
   leaf: 0x1800243 25166403 (12: nrow: 422 rrow: 422)
   leaf: 0x1800244 25166404 (13: nrow: 422 rrow: 422)
   leaf: 0x1800245 25166405 (14: nrow: 422 rrow: 422)
   leaf: 0x1800246 25166406 (15: nrow: 422 rrow: 422)
   leaf: 0x1800247 25166407 (16: nrow: 422 rrow: 422)
   leaf: 0x1800248 25166408 (17: nrow: 422 rrow: 422)
   leaf: 0x1800249 25166409 (18: nrow: 422 rrow: 422)
   leaf: 0x180024a 25166410 (19: nrow: 422 rrow: 422)
   leaf: 0x180024b 25166411 (20: nrow: 422 rrow: 422)
   leaf: 0x180024c 25166412 (21: nrow: 422 rrow: 422)
   leaf: 0x180024d 25166413 (22: nrow: 288 rrow: 288)
----- end tree dump
system@ANBOB> select blevel,leaf_blocks,distinct_keys from dba_indexes where index_name='IDX_TESTMAP_ID1NULL';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ----------- -------------
         1          24          9902
         
和视图里显示是一直的

note: 普通了b树索引是对null不可能走索引的,不讨论cluster table,但是可以通过建立复合索引来使where is null 走索引,上面的例子consistent gets从30降到了17

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Delphia Chern | #1
    2011-12-21 at 08:32

    Straight to the point and well written! Why can’t everyone else be like this?