Index Tuning
Oracle indexes can be tuned looking to three main characteristics: SELECTIVITY: you can calculate index selectivity using this formula: SELECT DISTINCT_KEYS/NUM_ROWS SELECTIVITY FROM DBA_INDEXES / Values range from 0 to 1, where 1 is the optimal value (UNIQUE indexes and PK), while with values near to 0 performance is lower CLUSTERING FACTOR: measures how many keys are stored in a single index block. This value is stored in DBA_INDEXES.CLUSTERING_FACTOR column. If it’s near to DBA_INDEXES_BLOCKS value then the index is good, if instead it’s near to DBA_INDEXES.NUM_ROWS performance is lower BLEVEL: Oracle indexes are stored in a B-TREE structure where...