Oracle 利用10053处理不走索引语句一则_不走索引 10053-优快云博客

本文介绍了一个关于Oracle数据库中无效索引导致全表扫描的问题及解决方法。通过分析执行计划和跟踪文件，发现索引处于不可用状态，最终通过重建索引来优化SQL执行效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

语句：

select count(1) from DW_PAGE_VIEW_FACT a where a.cookie_id='dd2a760b704e3936';

执行计划：

SQL> explain plan for select count(1) from DW_PAGE_VIEW_FACT a where a.cookie_id='dd2a760b704e3936';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 53213563

-----------------------------------------------------------------------------------------------
| Id  | Operation      | Name   | Rows  | Bytes | Cost (%CPU)| Time  | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |    |     1 |    17 | 37862   (1)| 00:07:35 |  |  |
|   1 |  SORT AGGREGATE      |    |     1 |    17 |       |   |  |  |
|   2 |   PARTITION RANGE ALL|    |    12 |   204 | 37862   (1)| 00:07:35 |     1 |   630 |
|*  3 |    TABLE ACCESS FULL | DW_PAGE_VIEW_FACT |    12 |   204 | 37862   (1)| 00:07:35 |     1 |   630 |
-----------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A"."COOKIE_ID"='dd2a760b704e3936')

15 rows selected.

可以看到虽然通过cookie_id查找，但仍然走的全表扫描，看下表上索引的情况

SQL> select index_name,table_name,column_name from dba_ind_columns where table_name='DW_PAGE_VIEW_FACT';

INDEX_NAME     TABLE_NAME          COLUMN_NAM
--------------- -------------------- ----------
IND_PAGE_VIEW   DW_PAGE_VIEW_FACT    SESSION_ID
IDX_PAGE_COOKIE DW_PAGE_VIEW_FACT    COOKIE_ID

设置10053跟踪计划生成

SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.

SQL> select count(1) from zybi.DW_PAGE_VIEW_FACT a where a.cookie_id='dd2a760b704e3936';
  COUNT(1)
----------
 26
SQL> alter session set events '10053 trace name context off';
Session altered.

查看跟踪文件：
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: DW_PAGE_VIEW_FACT Alias: A (Using composite stats)
    #Rows: 4141249 #Blks: 139516 AvgRowLen: 223.00
Index Stats::
Index: IDX_PAGE_COOKIE Col#: 8
    LVLS: 2 #LB: 14804 #DK: 258768 LB/K: 1.00 DB/K: 1.00 CLUF: 515320.00
    UNUSABLE
Index: IND_PAGE_VIEW Col#: 10
    USING COMPOSITE STATS
    LVLS: 2 #LB: 36272 #DK: 448128 LB/K: 1.00 DB/K: 1.00 CLUF: 281753.00
Access path analysis for DW_PAGE_VIEW_FACT
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for DW_PAGE_VIEW_FACT[A]
Column (#8):
    NewDensity:0.000003, OldDensity:0.000133 BktCnt:254, PopBktCnt:30, PopValCnt:1, NDV:291648
Table: DW_PAGE_VIEW_FACT Alias: A
    Card: Original: 4141249.000000 Rounded: 13 Computed: 12.52 Non Adjusted: 12.52
Access Path: TableScan
    Cost: 37862.32 Resp: 37862.32 Degree: 0
      Cost_io: 37787.00 Cost_cpu: 2401579483
      Resp_io: 37787.00 Resp_cpu: 2401579483
Best:: AccessPath: TableScan
         Cost: 37862.32 Degree: 1 Resp: 37862.32 Card: 12.52 Bytes: 0

***************************************

很不幸IDX_PAGE_COOKIE处于UNUSABLE状态，当前库之所以关于该表的语句能正常执行，是因为skip_unusable_indexes=true，
修改后会报错：

SQL> insert into zybi.DW_PAGE_VIEW_FACT select * from zybi.DW_PAGE_VIEW_FACT where rownum=1;
insert into zybi.DW_PAGE_VIEW_FACT select * from zybi.DW_PAGE_VIEW_FACT where rownum=1
*
ERROR at line 1:
ORA-01502: index 'ZYBI.IDX_PAGE_COOKIE' or partition of such index is in
unusable state

这个索引失效很可能是移动表或表压缩造成的，需要重建索引：

SQL> alter index zybi.IDX_PAGE_COOKIE rebuild;

Index altered.

SQL> select degree from dba_indexes where index_name='IDX_PAGE_COOKIE';

DEGREE
------------------------------
1

再次查看执行计划：

SQL>  explain plan for select count(1) from zybi.DW_PAGE_VIEW_FACT a where a.cookie_id='dd2a760b704e3936';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3653499665

------------------------------------------------------------------------------
| Id  | Operation   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   1 |  17 |   3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |   1 |  17 |   |     |
|*  2 |   INDEX RANGE SCAN| IDX_PAGE_COOKIE |  12 | 204 |   3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------

   2 - access("A"."COOKIE_ID"='dd2a760b704e3936')

14 rows selected.