stay Oracle in , What is index selectivity ？
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 .