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
Straight to the point and well written! Why can’t everyone else be like this?