对失效索引，9i提示不能用，10g直接跳过索引走全表扫描：

最新推荐文章于 2025-10-22 18:13:40 发布

转载最新推荐文章于 2025-10-22 18:13:40 发布 · 78 阅读

本文探讨了在不同Oracle版本中遇到索引不可用状态的问题，包括错误ORA-01502的处理方法。通过对比9i、10g和11g版本下索引不可用时的不同行为及解决方案，帮助读者理解如何调整参数skip_unusable_indexes以避免此类问题。

9I的情况：

@>select * from wxh_tbd where object_id=1;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'WXH_TBD'
   2    1     INDEX (RANGE SCAN) OF 'T' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          2 consistent gets
          1 physical reads
          0 redo size
       1093 bytes sent via SQL*Net to client
        456 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

@>alter table wxh_tbd move;

Table altered.

@>select * from wxh_tbd where object_id=1;
select * from wxh_tbd where object_id=1
*
ERROR at line 1:
ORA-01502: index 'TEST.T' or partition of such index is in unusable state

11G的情况：

apollo@CRMG>select * from wxh_tbd where object_id=1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 653060453

---------------------------------------------------------------------------------------
| Id | Operation                   | Name    | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |   207 |     1   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| WXH_TBD |     1 |   207 |     1   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | T       |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        312 recursive calls
          0 db block gets
        103 consistent gets
          1 physical reads
          0 redo size
       1283 bytes sent via SQL*Net to client
        513 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

alter table wxh_tbd move;

Table altered.

select * from wxh_tbd where object_id=1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3295978849

-----------------------------------------------------------------------------
| Id | Operation         | Name    | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     3 |   621 |    72   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| WXH_TBD |     3 |   621 |    72   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         24 recursive calls
          0 db block gets
        323 consistent gets
        249 physical reads
         72 redo size
       1283 bytes sent via SQL*Net to client
        513 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

10G后，多了一个参数去控制。skip_unusable_indexes。10G后skip_unusable_indexes默认设置为true.如果设置成false，也会报出和9I一样的错了。

apollo@CRMG>alter system set skip_unusable_indexes=false;

System altered.

apollo@CRMG>select * from wxh_tbd where object_id=1;
select * from wxh_tbd where object_id=1
*
ERROR at line 1:
ORA-01502: index 'APOLLO.T' or partition of such index is in unusable state

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-680612/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-680612/