watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

stay Oracle in , What is index selectivity ?


     
          Answer section          



Index selectivity (Index Selectivity, Index selectivity or index selectivity ) It refers to the ratio of the number of records with different values in the index column to the total number of records in the table . The range of index selectivity is [0,1]. for example , The number of records in a table is 1000 strip , The value of the index column of this table is only 900 Different values ( Yes 100 Is it the same or empty ), therefore , The selectivity of the column index is 900/1000=0.9. For index selectivity , The higher the value, the more efficient the index of the column will be . You can use the following SQL To calculate index selectivity :

1SELECT COUNT(DISTINCT NAME)/COUNT(*) FROM TB_A;
     

The advantage of this method is that the selectivity of the index can be evaluated before the index is created . When the index is collected with the latest statistics , You can use the following SQL Statement query index selectivity :

1SELECT INDEX_NAME,DISTINCT_KEYS/NUM_ROWS SELECTIVITY FROM DBA_INDEXES;
     

Beyond all doubt , The selectivity of primary key is 1. The closer the selectivity is 1, So the better the index is .