在Oracle数据库的日常运维中，经常发现这样一些语句，这些语句对一张大表（数据量由几百万行至几千万行）执行含有几个条件的查询，然后返回一行或者是几行，这些语句的最简单的形式像下面这个样子：
select * from test where id< 1000 and rownum=1;
这类语句在awr报告中也偶尔可以看到，它们的逻辑读非常大，返回的行数却不多。这种语句需要优化吗，需要怎么优化，怎么查看这类语句是否存在性能瓶颈。下面通过一个简单的例子说明这类语句怎么查看性能以及如何优化。首先第一步是创建一张测试表，使用下面的语句：
createtable model_tab as(select*from dual model dimension by(0 d) measures (0 rnum) rules iterate(1000000)(rnum[ITERATION_NUMBER]= ITERATION_NUMBER+1));
上面的语句使用的是select 的model语句，model语句定义了一个一维的立方体，这个数组的维度值是Oracle数据库dual表的d列，值是循环的次数加1，总共循环1000000次，创建表的数据是1000000行。
运行下面的语句查询表，以rnum为条件查询一下这张表，语句如下：
select*from model_tab where rnum<=1000and rownum=1;
在没有索引的情况下，语句的执行计划如下：
PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------Plan hash value:2500570731--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time|--------------------------------------------------------------------------------|0|SELECT STATEMENT ||1|10|3(0)|00:00:01||*1|COUNT STOPKEY |||||||*2|TABLE ACCESS FULL| MODEL_TAB |3|30|3(0)|00:00:01|--------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT -----------------------------------------------------------------------------------------------------------------------------------1- filter(ROWNUM=1)2- filter("RNUM"<=1000)
从上面的执行计划可以看出，这条语句执行了全表扫描，总共扫描了三行数据，使用了count stopkey操作。在model_tab表的rnum列上创建索引
create index idx_rnum on model_tab(rnum);
索引创建后，再查看语句的执行计划，变成下面这个
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value:2837899601--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time|--------------------------------------------------------------------------------------------------|0|SELECT STATEMENT ||1|10|4(0)|00:00:01||*1|COUNT STOPKEY |||||||2|TABLE ACCESS BY INDEX ROWID BATCHED| MODEL_TAB |3|30|4(0)|00:00:01||*3| INDEX RANGE SCAN | IDX_RNUM |1000||3(0)|00:00:01|--------------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1- filter(ROWNUM=1)3- access("RNUM"<=1000)
优化器使用了刚才创建的索引，执行了范围扫描，扫描了1000行数据，可见Oracle优化器认为这个计划比原来的全表扫描性能更好，可是，比较这两个执行计划，从返回行数及成本来看，没有明显的差别，甚至使用索引的范围扫描扫描的行数更多。但是，Oracle优化器为什么会选择使用索引，这里优化的选择正确吗，看一下这条语句再有无索引的情况下实际执行的统计信息，没有索引的情况下，统计信息如下：
Statistics ----------------------------------------------------------0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 621 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory)0 sorts (disk)1 rows processed
创建索引后，统计信息如下：
0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 623 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory)0 sorts (disk)1 rows processed
还是没有明显的差别，都是4个一致性读，难道有没有索引这条语句的执行效率相差无几，改一下语句里rnum的条件看一看
select*from model_tab where rnum>=100000and rownum=1;
rnum的条件改成大于10万，再看一下有无索引情况下语句执行的状态统计，在没有索引的情况下：
Statistics ----------------------------------------------------------0 recursive calls 0 db block gets 208 consistent gets 0 physical reads 0 redo size 622 bytes sent via SQL*Net to client 417 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory)0 sorts (disk)1 rows processed
在有索引的情况下
Statistics ----------------------------------------------------------0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 626 bytes sent via SQL*Net to client 417 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory)0 sorts (disk)1 rows processed
从上面的语句执行的统计信息来看，在有索引的情况下，逻辑读（4个）明显少于无索引的情况下的逻辑读（208）个，有着明显的优势。
从上面的比较来看，在没有索引，执行全表情况下，语句执行的效率和执行条件有关系，这时应为count stopkey操作时，在找到符合条件的数据时，就会停止全表扫描，返回数据，语句的执行效率有查找的条件有关系，也同数据分布有关系，执行效率播放较大。而在有索引的情况下，执行范围扫描，扫描到符合条件的数据后也会停止扫描，返回数据，则语句的执行效率相对稳定，这时由btree索引的性质决定的。如果在数据库的日常运维和awr报告中发现此类语句单词执行逻辑读较多或占用cpu时间过长，就要检查是否已经为此类语句建立有效的索引，因为如果执行的是范围索引，此类语句的逻辑读应该较小，尤其是在查询的条件有多个时，创建合适的符合索引更是十分必要，对语句的性能提高也更大，这时因为在多个条件下，很可能会在扫描完全表才能发现符合条件的数据。
