You need to compare the clustering factor vs leaf_blocks. If clustering factor is euqual or similar to leaf_blocks, so you have a high clustering factor.
This means that the optimized will avoid to use the index for range scan queries.
In this situation, you should evaluate to rebuild the index.
set linesize 300
SELECT index_name,owner,num_rows,clustering_factor,LAST_ANALYZED,leaf_blocks, blevel,DECODE(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2,'OK BLEVEL',3,'OK BLEVEL','BLEVEL HIGH') OK
FROM dba_indexes
where owner not in ('SYSTEM','SYS','OUTLN','TSMSYS','DBSNMP','WMSYS','EXFSYS','CTXSYS','XDB','DMSYS','ORDSYS','MDSYS','OLAPSYS','SCOTT')
and blevel > 1
order by blevel desc;
| INDEX_NAME | OWNER | NUM_ROWS | CLUSTERING_FACTOR | LAST_ANALYZED | LEAF_BLOCKS | BLEVEL | OK |
| TRFCQIN~3 | SAPSR3 | 22.236 | 751 | 06/19/2014 12:05:46 | 1.890 | 6 | BLEVEL HIGH |
| E071~0 | SAPSR3 | 8.035.567 | 154.567 | 11/13/2015 05:10:32 | 54.124 | 3 | OK BLEVEL |
| E071~1 | SAPSR3 | 7.728.900 | 6.022.800 | 11/13/2015 05:09:55 | 74.272 | 3 | OK BLEVEL |
| SWP_NODEWI~0 | SAPSR3 | 6.607.567 | 2.393.433 | 11/13/2015 05:40:25 | 68.745 | 3 | OK BLEVEL |
| SWP_NODEWI~NOD | SAPSR3 | 6.546.567 | 3.300.200 | 11/13/2015 05:41:04 | 69.892 | 3 | OK BLEVEL |
| SWW_WI2OBJ~001 | SAPSR3 | 8.028.767 | 2.573.467 | 11/13/2015 05:42:00 | 84.173 | 3 | OK BLEVEL |
| SWW_WI2OBJ~002 | SAPSR3 | 8.062.667 | 2.307.700 | 11/13/2015 05:42:35 | 99.855 | 3 | OK BLEVEL |
| SWW_WI2OBJ~003 | SAPSR3 | 7.900.033 | 1.787.967 | 11/13/2015 05:43:11 | 69.127 | 3 | OK BLEVEL |
| SCPRVALS~0 | SAPSR3 | 6.901.233 | 120.500 | 11/13/2015 05:32:47 | 65.224 | 3 | OK BLEVEL |
| WBCROSSGT~0 | SAPSR3 | 4.314.567 | 3.429.733 | 11/13/2015 05:54:16 | 50.897 | 3 | OK BLEVEL |
| E071K~0 | SAPSR3 | 15.575.300 | 403.700 | 11/13/2015 05:11:47 | 171.030 | 3 | OK BLEVEL |
| DBTABLOG~0 | SAPSR3 | 16.781.200 | 1.431.600 | 09/25/2016 21:11:29 | 155.266 | 3 | OK BLEVEL |
| E071K~ULI | SAPSR3 | 15.354.902 | 365.646 | 11/13/2015 05:10:55 | 111.682 | 3 | OK BLEVEL |
| CDCLS~0 | SAPSR3 | 64.080.900 | 63.354.900 | 09/25/2016 21:08:31 | 923.150 | 3 | OK BLEVEL |
| FAGL_SPLINFO~0 | SAPSR3 | 10.678.441 | 3.046.656 | 09/25/2016 21:20:42 | 108.398 | 3 | OK BLEVEL |
| ZARHRT_FEU007~0 | SAPSR3 | 50.963.200 | 4.181.500 | 11/13/2015 05:58:29 | 674.054 | 3 | OK BLEVEL |
Comments