Orcale Tip: Indexes Vs Full Table Scans

Indexes Vs Full Table Scans

The ORACLE database offers indexes on tables to speed up retrieval and an optimiser to decide on the most efficient way of using them.  Under some conditions, the optimiser will decide that it cannot use indexes and will perform a full table scan instead. When Oracle’s rules based optimisation is used, Oracle's use of indexes can only be controlled indirectly

·      If retrieving a value that occurs in more than 15% of the table, it is more efficent to use a full table scan (rather scan the indexes first).

·      If retrieving < 15%, you must take care not to disable indexes accidentally. (Unless this provides a more efficent path for the execution of a complex query).

Be aware, any type of ‘function’ used against a column, such as MAX, SUM, TO_CHAR, TO_DATE  etc..... will disable any indexes on that column.

As a rule, columns that have few distinct values (eg gender) should not be indexed..

How to avoid disabling indexes (accidentally)

To avoid disabling indexes unintentionally:

Don't modify indexed columns, shown below by the underline, in the WHERE clauses of SELECT statements, for instance;

   use: WHERE      test_number = TO_NUMBER('999')

   not: WHERE      TO_CHAR(test_number) = '999'

   use: WHERE      stringA  like  stringB%

   not: WHERE      SUBSTR(stringA) = stringB

Use appropriate data types for the columns being selected, otherwise ORACLE will implicitly modify the database columns, e.g. if ACCOUNT is an indexed char field and the code is written as follows:

   WHERE account =  12345

ORACLE will perform a TO_NUMBER on ACCOUNT, thus disabling the index. To avoid this use quotes around the number.

Avoid using the NOT or != operator on an indexed field.  ORACLE assumes that the number of rows NOT matching will be greater, so it ignores the index. To force the use of the index use >= instead of NOT <.

Avoid testing for  NULL or  NOT NULL  on an indexed field,  as this will never use the index (e.g. on a numeric field you could use >= 0 instead of NOT NULL, if most values will be NULL).

How to disable an index (on purpose)

Indexes are disabled by modifying the indexed column in the WHERE clause.  The standard methods are:

   WHERE (empno + 0) > 1000                   -for numbers

   WHERE (ename || ' ') = 'BROWN'             -for chars

   WHERE add_months(hiredate,0) < sysdate          -for dates

Using the LIKE operator

This will use indexes if the column is char, or the comparison string begins with a character, e.g.:

ename like 'S%'                        - will use an index on ename

ename like '%S%'                    - will not use the index

 Set Notation

Remember that all set functions (union, minus and intersect) carry out an implicit distinct upon rows returned.  This has a considerable performance overhead as Oracle has to build temporary tables to carry out the distinct function.

Using Indexes - Summary

When using INDEXES:

1.       THINK about whether you should be using indexes or full table scans.

2.       AVOID disabling indexes accidentally.

3.       PUT the most precise condition FIRST in the WHERE clause.

4.       PUT the most precise OR condition FIRST, where you have several ORs.

5.       PUT the most precise OR/AND condition LAST, where you have several OR/ANDs (i.e. where (X and Y) or (Y and Z)  ).

When JOINING tables:

·         Make sure there's an index on one of the join conditions

·         Put the largest table with the smallest number of relevant rows LAST in the FROM clause.

·         If 3 or more tables are to be joined, put the join that will result in the smallest number of rows LAST of the join conditions.