TIP : - How do I determine Index Selectivity ?

Solution :-

There are two methods to determine index selectivity 

Method 1. Analyze table and query the data dictionary view.
Method 2. Determine   distinct values and total number of rows  from table itself.

Analyze table and query the data dictionary view.

Index selectivity is determined as

INDEX SELECTIVITY = [  No of distinct values / (Total  number of rows) ] * 100

The percentage close to hundred indicates good selectivity and hence better index.


Step 0. connect as sys
step 1.analyze table  <table_owner>.<table_name> compute statistics.

step2. select  column_name,num_distinct from dba_tab_columns
           where table_name= <table_name>
          and     owner         = < table_owner>
          and column_name = < table_col_to_be_index>

Step 3. select num_rows from dba_tables
            where table_name=<table_name>
            and    owner         = <table_owner>


step 4 .   Calculate index selectivity =  [num_distinct /(num_rows) ] * 100
num_distinct and num_rows are obtained from step2 and step 3.


Determine  distinct values and total number of rows from table itself.

Step 0. connect to user who owns the table

Step 1.  select count(distinct < column_name>)  num_distinct  from  <table_name>;

Step 2. select count(*)  num_rows from <table_name>;

step 3.   Calculate index selectivity =  [num_distinct /(num_rows) ] * 100
num_distinct and num_rows are obtained from step1 and step 2.

End of Tip	Welcome to send comments or feedback at Wadhwa_s@Hotmail.com
Sameer Wadhwa	Copyright © Oracle Techniques All Rights Reserved