As a general rule, tables often queried for less than 15% of the rows of the table should contain indexes. This value could be higher if all data is taken from an

index, or if indexed columns may be used to enter other tables.

The index selection of an index is the proportion of the number of separate values in the indexed columns to the number of records in the table. The perfect selective is 1. Only single indexes on NOT NULL columns can achieve such a selectivity.

________________________________________________________________________

1- Strong Selectivity Example :

The selectivity of this index is 88'000/10'000 = 0,88, a table with 100'000 records and one of its Indexed Columns with 88'000 distinct values. Oracle produces implicit indexes of all single and principal keys that you specify with integrity constraints on all columns. These are the most selective and efficient indexes for performance optimization. The index selectivity is the percentage of rows in a table that have the same index column value. The selectivity of an index is nice if few rows are equal.

________________________________________________________________________

2- Weak Selectivity Example :

If only 500 different values are shown in an index on the table of 100'000 records, then the index selectivity is 500/100'000 = 0.005, and a query using the index constraint would return 100'000/500 = 200 records for the same values. The full table scan is certainly more effective than using such an index, which takes far more I / O to scan the index and table repeatedly.

________________________________________________________________________

3- How to Measure Index Selectivity ?

________________________________________________________________________

3.1- Manually Measurement

The proportion of the number of separate values to the total number of rows is the columns' selectivity. This method is useful to measure the index selectivity before it is formed.

select count (distinct job) "Distinct Values" from emp;

Distinct Values ---------------               5

select count(*) "Total Number Rows" from emp;

Total Number Rows -----------------                14

Selectivity = Distinct Values / Total Number Rows             = 5 / 14             = 0.35

________________________________________________________________________

3.2- Automatically Measurement

The selectivity of an index can be calculated by dividing the number of individual index values by the number of rows in the table.

create index idx_emp_job on emp(job); analyze table emp compute statistics;

select distinct_keys from user_indexes where table_name = 'EMP' and index_name = 'IDX_EMP_JOB';

DISTINCT_KEYS -------------             5

select num_rows from user_tables where table_name = 'EMP';

NUM_ROWS ---------        14

Selectivity = DISTINCT_KEYS / NUM_ROWS = 0.35

________________________________________________________________________

Each Individual Column Selectivity

If the table has been analyzed, the selectivity of each column can also be individually examined by USER TAB COLUMNS.

select column_name, num_distinct from user_tab_columns where table_name = 'EMP';

COLUMN_NAME                     NUM_DISTINCT ______________________________________________________ EMPNO                                     14 ENAME                                     14 JOB                                        5 MGR                                        2 HIREDATE                                  13 SAL                                       12 COMM                                       4 DEPTNO                                     3

________________________________________________________________________

3.3- How to choose Composite Indexes ?

More than one main column is used in a composite index. Composite indexes can provide additional benefits over individual column indexes.

— Better Selectivity: Often a composite index with good selectivity may be combined with two or three columns, each with bad selectivity.

— Adding Data Storage: If the query's columns are all in the composite index, Oracle can return the values without accessing the table from the index. In this case, however, an IoT (Index Only Table) is easier to use.

SQL statements which use a composite index access path if the statement contains constructs using an index lead element. A number of one or more columns in the list of columns in the CREATE INDEX statement that created the index is the leading portion of an index. Consider the statement CREATE INDEX from Build INDEX:

CREATE INDEX idx_composite ON mytab (x, y, z);

These column combinations are the leading index parts: X, XY, and XYZ.

These column combinations are not the leading index parts: YZ and Z.

________________________________________________________________________

Consider developing an index of columns frequently used in combination with AND operators in WHERE clause situations , particularly if the combined selectivity of each column is greater than individual selectivity. In SQL declarations, consider indexing columns sometimes used to enter tables.

• Isn't it better to first normalize your data and then try to select them?
• Also, there are some well-known methods like F-score or CDET which help you with your selectivity.
• the last suggestion is to use a good feature extraction method based on your data set of course.