9i索引范围扫描逻辑读问题
在论坛里面看到一个帖子，发现9i在默写情况下，执行索引范围扫描，并没有过滤结果，导致大量不必要的逻辑读。
看一个具体的例子吧：
SQL> CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(30));
表已创建。
SQL> INSERT INTO TEST SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;
已创建31400行。
SQL> COMMIT;
提交完成。
SQL> CREATE INDEX IND_TEST_NAME ON TEST (NAME);
索引已创建。
下面执行一个查询，这个查询的WHERE条件对NAME字段进行限制，不过LIKE操作添加了前通配符，所以不能通过索引来快速定位结果：
SQL> SET AUTOT TRACE
SQL> SELECT * FROM TEST WHERE NAME LIKE '%TEST';
已选择21行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
153 consistent gets
0 physical reads
0 redo size
800 bytes sent via SQL*Net to client
375 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
可以看到，Oracle选择了全表扫描，如果使用HINT指定索引：
SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST';
已选择21行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Bytes=120)
2 1 INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=2 Card=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
24243 consistent gets
0 physical reads
0 redo size
786 bytes sent via SQL*Net to client
375 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
问题出现了，Oracle选择了索引范围扫描，但是通过统计信息可以看到，查询导致了大量的逻辑读，已经远远超过了全表扫描的大小，显然Oracle没有在索引扫描后排除掉不满足结果的记录，而是将所有的ROWID全部返回到上一步，根据这些ROWID读取记录后，Oracle才进行的过滤。
而这种操作的代价导致了严重的性能问题，如果将查询改变一下，增加一个排序，尝试让Oracle选择ORACLE全索引扫描，看看执行结果：
SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST' ORDER BY NAME;
已选择21行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Bytes=120)
2 1 INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=2 Card=1)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
24243 consistent gets
0 physical reads
0 redo size
786 bytes sent via SQL*Net to client
375 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
Oracle仍然选择了索引范围扫描，问题依旧，下面修改一下SESSION级的OPTIMIZER_INDEX选项：
SQL> SET AUTOT OFF
SQL> SHOW PARAMETER OPTIMIZER_INDEX
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
SQL> ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 100;
会话已更改。
SQL> ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;
会话已更改。
SQL> SET AUTOT TRACE
SQL> SELECT /*+ FIRST_ROWS */ * FROM TEST WHERE NAME LIKE '%TEST' ORDER BY NAME;
已选择21行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=7 Card=4 Bytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=7 Card=4 Bytes=120)
2 1 INDEX (FULL SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=26 Card=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
168 consistent gets
0 physical reads
0 redo size
786 bytes sent via SQL*Net to client
375 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
21 rows processed
这时大量逻辑读的问题就不存在了。Oracle的全索引扫描时，会根据索引先过滤WHERE条件，然后将过滤结果的ROWID去访问表。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
这是9204中的现象，在9201中表现有所区别，只要加上ORDER BY语句，即使执行计划仍然是索引范围扫描，依然可以避免大量的逻辑读，而不带ORDER BY的查询语句，索引范围扫描就会导致大量的逻辑读：
SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Bytes=120)
2 1 INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
42 recursive calls
0 db block gets
20590 consistent gets
121 physical reads
0 redo size
490 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST' ORDER BY NAME;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=4 Bytes=120)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=1 Card=4 Bytes=120)
2 1 INDEX (RANGE SCAN) OF 'IND_TEST_NAME' (NON-UNIQUE) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
126 consistent gets
0 physical reads
0 redo size
490 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> SET AUTOT OFF
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for Linux: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
而在10g中，则Oracle直接会采用索引全扫描，不会产生这个问题：
SQL> SET AUTOT TRACE
SQL> SELECT /*+ INDEX(TEST) */ * FROM TEST WHERE NAME LIKE '%TEST';
已选择17行。
执行计划
----------------------------------------------------------
Plan hash value: 1655750108
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 690 | 1485 (1)| 00:00:21 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 23 | 690 | 1485 (1)| 00:00:21 |
|* 2 | INDEX FULL SCAN | IND_TEST_NAME | 2854 | | 134 (1)| 00:00:02 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME" LIKE '%TEST')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
150 consistent gets
0 physical reads
0 redo size
750 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
17 rows processed
SQL> SET AUTOT OFF
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
看来这个问题属于9i的bug，不过这个bug在9201和9204上的表现还有所区别。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-539651/，如需转载，请注明出处，否则将追究法律责任。
