oracle 索引失效分析步骤

 一般先看语句的执行计划：

----详细计划的查看方式，打开sqlplus

然后执行下面语句就可以了

-----

SQL> set autotrace traceonly
SQL> select * from dwh_etb_monthly t where t.data_date = '2011-03-31';

已选择253619行。

执行计划
----------------------------------------------------------
Plan hash value: 1519047794

----------------------------------------------------------------------------

-----

| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| T
    |

----------------------------------------------------------------------------

-----

|   0 | SELECT STATEMENT  |                 |   273K|   103M| 14060   (1)| 0

:49 |

|*  1 |  TABLE ACCESS FULL| DWH_ETB_MONTHLY |   273K|   103M| 14060   (1)| 0

:49 |

----------------------------------------------------------------------------

-----

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

   1 - filter("T"."DATA_DATE"='2011-03-31')

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      67033  consistent gets
      51218  physical reads
          0  redo size
   42214108  bytes sent via SQL*Net to client
     186393  bytes received via SQL*Net from client
      16909  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     253619  rows processed

 

 

重新建立索引的步骤

------

drop index IDX_ETB_DDATE1 ;

analyze table dwh_etb_monthly compute statistics ;

analyze table dwh_etb_monthly compute statistics for all indexed columns;

analyze table dwh_etb_monthly compute statistics for table for all indexes for all indexed columns;

create index idx_dwh_ddate on dwh_etb_monthly(data_date)

select * from dwh_etb_monthly t where t.data_date = '2011-03-31'