How to choose a correct index on table and the order of columns for the index?
In Oracle, when a query is slow, first thing we check is if the query is using correct index or if any full table scan(FTS).
Please note that Full table scan is not a bad thing.Sometime ,Full table scan(FTS) perform better than index can when the no blocks it has to scan
through is lesser.fo ex ,when the no of data blocks( all blocks in a table) read for full table scan is lesser than the no of blocks read fron index + no of data blocks access
(Ref:How to find out the logical IO?)
" How many blocks the query supposed to read to satisfy the given criteria based on the current data or trend"
" Creating index will create additional overhead to insert and more overhead to delete and update statements"
" For the frequently used queries, based on the where critieria
if the No of blocks read by the index access + data blocks >= total no of blocks, then index creation may give adverse effect"
1.Consider indexing keys that are used frequently in WHERE clauses.
2. Consider indexing keys that are used frequently to join tables in SQL statements.
3.Choose index keys that have high selectivity. ( Ref: How to determine the Selectivity ? )
4. In a composite index, choose the order of column with more selectivity as leading columns.....
5.Do not use standard B-tree indexes on keys or expressions with few distinct values.
Such keys or expressions usually have poor selectivity and therefore do not optimize performance unless the frequently selected key values appear
less frequently than the other key values. You can use bitmap indexes effectively in such cases, unless the index is modified frequently,
as in a high concurrency OLTP application.
INSERT and DELETE statements that modify indexed tables take longer than if there were no index.
Such SQL statements must modify data in indexes as well as data in tables. They also generate additional undo and redo.
A WHERE clause that uses a function, other than MIN or MAX, or an operator with an indexed key does not make available the access path
that uses the index except with function-based indexes.
UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table.
( This will avoid most of the deadlocks)
UPDATEs, and DELETEs and the use of the space required to store the index. You might want to experiment by
comparing the processing times of the SQL statements with and without indexes. You can measure processing time with the SQL trace facility.
alter index owner.indexname invisible;
- test query
alter index owner.indexname visible;
10
* Unique Keys - these are given an index automatically (ditto)
* Foreign Keys - these are not automatically indexed, but you should add one to avoid performance issues when the constraints are checked
Seek OEM administrator to get this acccess in dev/uat if you want to run this advory though gui interface.
There are various methods to find out the logical IOs like tracing,awr report/autotrace.Let us see one of the easiest method using auto trace
case 1 How many logical ios for a full table scan
SQL> set linesize 200
SQL> set autotrace traceonly
SQL> select object_name from PERFORMANCE;
15972 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1331860161
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15972 | 296K| 63 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PERFORMANCE | 15972 | 296K| 63 (2)| 00:00:01 |
---------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1263 consistent gets
0 physical reads
0 redo size
507677 bytes sent via SQL*Net to client
12227 bytes received via SQL*Net from client
1066 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15972 rows processed
Case 2: Find out how many logical ios for an index scan
SQL> select object_name from PERFORMANCE where object_name='HELLO';
15972 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2578635553
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15972 | 95832 | 66 (0)| 00:00:01 |
| 1 | INDEX RANGE SCAN| PERF_IDX | 15972 | 95832 | 66 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1132 consistent gets
0 physical reads
0 redo size
275151 bytes sent via SQL*Net to client
12227 bytes received via SQL*Net from client
1066 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15972 rows processed
How to determine the Selectivity ?
Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.
Index Selectivity :
The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index.
The ideal selectivity is 1. Such a selectivity can be reached only by unique indexes on NOT NULL columns.
Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints.
These indexes are the most selective and the most effective in optimizing performance. The selectivity of an index is the percentage of rows
in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value.
lf an index on a table of 100'000 records had only 500 distinct values, then the index's selectivity is 500 / 100'000 = 0.005 and
in this case a query which uses the limitation of such an index will retum 100'000 / 500 = 200 records for each distinct value.
It is evident that a full table scan is more efficient as using such an index where much more I/O is needed to scan repeatedly the index and the table.
The ratio of the number of distinct values to the total number of rows is the selectivity of the columns.
This method is useful to estimate the selectivity of an index before creating it.
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
Automatically measure index selectivity
We can determine the selectivity of an index by dividing the number of distinct indexed 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
Selectivity of each individual Column
Assuming that the table has been analyzed it is also possible to query USER_TAB_COLUMNS to investigate the selectivity of each column individually.
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
A composite index contains more than one key column. Composite indexes can provide additional advantages over single column indexes.
Better Selectivity Sometimes two or more columns, each with poor selectivity, can be combined to form a composite index with good selectivity.
Oracle can return these values from the index without accessing the table. However in this case, it's better to use an IOT (Index Only Table).
A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index.
