索引和NULL值

来源：互联网发布：n86软件下载编辑：程序博客网时间：2024/05/18 01:11
<pre name="code" class="sql"><pre name="code" class="sql">索引和NULL值create table test10(id int,name char(10))beginfor i in 1 .. 100000loopinsert into test10 values(i,'a'||i);commit;end loop;end;create index test10_idx1 on test10(id);BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',                                tabname          => 'TEST10',                                estimate_percent => 100,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;insert into test10 values(null,'a');1.SQL> select count(*) from test10;执行计划----------------------------------------------------------Plan hash value: 260470369---------------------------------------------------------------------| Id  | Operation   | Name   | Rows  | Cost (%CPU)| Time     |---------------------------------------------------------------------|   0 | SELECT STATEMENT   |    |  1 | 66   (2)| 00:00:01 ||   1 |  SORT AGGREGATE    |    |  1 | |    ||   2 |   TABLE ACCESS FULL| TEST10 |100K| 66   (2)| 00:00:01 |---------------------------------------------------------------------统计信息----------------------------------------------------------  0  recursive calls  0  db block gets293  consistent gets  0  physical reads  0  redo size425  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processedSQL> select count(id) from test10;   执行计划----------------------------------------------------------Plan hash value: 3206018756-------------------------------------------------------------------------------------| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |     |  1 |  5 | 50   (0)| 00:00:01 ||   1 |  SORT AGGREGATE       |     |  1 |  5 | |    ||   2 |   INDEX FAST FULL SCAN| TEST10_IDX1 |100K|488K| 50   (0)| 00:00:01 |-------------------------------------------------------------------------------------统计信息----------------------------------------------------------  0  recursive calls  0  db block gets228  consistent gets  0  physical reads  0  redo size424  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processed2.SQL> select id from test10;已选择100001行。执行计划----------------------------------------------------------Plan hash value: 4117858598----------------------------------------------------------------------------| Id  | Operation  | Name   | Rows  | Bytes | Cost (%CPU)| Time   |----------------------------------------------------------------------------|   0 | SELECT STATEMENT  |   |   100K|   488K|66   (2)| 00:00:01 ||   1 |  TABLE ACCESS FULL| TEST10 |   100K|   488K|66   (2)| 00:00:01 |----------------------------------------------------------------------------统计信息----------------------------------------------------------  0  recursive calls  0  db block gets       6943  consistent gets  0  physical reads  0  redo size    1455977  bytes sent via SQL*Net to client      73741  bytes received via SQL*Net from client       6668  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)     100001  rows processedSQL>  select id from test10 where id is not null;已选择100000行。执行计划----------------------------------------------------------Plan hash value: 1361564665------------------------------------------------------------------------------------| Id  | Operation     | Name   | Rows  | Bytes | Cost (%CPU)| Time   |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |   |   100K|   488K|50   (0)| 00:00:01 ||*  1 |  INDEX FAST FULL SCAN| TEST10_IDX1 |   100K|   488K|50   (0)| 00:00:01 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("ID" IS NOT NULL)统计信息----------------------------------------------------------  1  recursive calls  0  db block gets       6880  consistent gets  0  physical reads  0  redo size    1455975  bytes sent via SQL*Net to client      73741  bytes received via SQL*Net from client       6668  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)     100000  rows processed3.SQL> select max(id) from test10;执行计划----------------------------------------------------------Plan hash value: 2324431888------------------------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   | |     1 |     5 |    66   (2)| 00:00:01 ||   1 |  SORT AGGREGATE    | |     1 |     5 |      |  ||   2 |   INDEX FULL SCAN (MIN/MAX)| TEST10_IDX1 |   100K|   488K|      |  |------------------------------------------------------------------------------------------统计信息----------------------------------------------------------  0  recursive calls  0  db block gets  2  consistent gets  0  physical reads  0  redo size422  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processed如果希望直接从索引返回数据,由于索引不存储NULL值,则需要显示说明 is not null4.SQL>     select max(id),min(id) from test10; 执行计划----------------------------------------------------------Plan hash value: 260470369-----------------------------------------------------------------------------| Id  | Operation   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT   |    |  1 |  5 | 66   (2)| 00:00:01 ||   1 |  SORT AGGREGATE    |    |  1 |  5 | |    ||   2 |   TABLE ACCESS FULL| TEST10 |100K|488K| 66   (2)| 00:00:01 |-----------------------------------------------------------------------------统计信息----------------------------------------------------------  1  recursive calls  0  db block gets293  consistent gets  0  physical reads  0  redo size486  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processed为什么不走索引呢？这条语句要同时获得两个值, oracle的INDEX FULL SCAN (MIN/MAX)是无法一次取到两个值的SQL>  select max(id),min(id) from test10 where id is not null;执行计划----------------------------------------------------------Plan hash value: 3206018756-------------------------------------------------------------------------------------| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |     |  1 |  5 | 50   (0)| 00:00:01 ||   1 |  SORT AGGREGATE       |     |  1 |  5 | |    ||*  2 |   INDEX FAST FULL SCAN| TEST10_IDX1 |100K|488K| 50   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("ID" IS NOT NULL)统计信息----------------------------------------------------------  1  recursive calls  0  db block gets228  consistent gets  0  physical reads  0  redo size486  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processedSQL> select (select max(id)from test10),(select min(id)from test10) from dual;执行计划----------------------------------------------------------Plan hash value: 3694443619------------------------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   | |     1 | |     2   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    | |     1 |     5 |      |  ||   2 |   INDEX FULL SCAN (MIN/MAX)| TEST10_IDX1 |   100K|   488K|      |  ||   3 |  SORT AGGREGATE    | |     1 |     5 |      |  ||   4 |   INDEX FULL SCAN (MIN/MAX)| TEST10_IDX1 |   100K|   488K|      |  ||   5 |  FAST DUAL   | |     1 | |     2   (0)| 00:00:01 |------------------------------------------------------------------------------------------统计信息----------------------------------------------------------  1  recursive calls  0  db block gets  4  consistent gets  0  physical reads  0  redo size522  bytes sent via SQL*Net to client415  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)
0 0