首页 » ORACLE 9i-23ai » 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
上一篇: mysql 快速复制数据库
下一篇: What is “save undo”?
目前这篇文章有1条评论(Rss)评论关闭。