It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

When Your Index will not be used - FULL TABLE SCAN

Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows. Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries. Otherwise, they just take up space and add overhead when the indexed columns are updated.

In this post I will be sharing various scenarios when Index will not be chosen by Optimizer. We will use the EXPLAIN PLAN feature to determine how the indexes are being used in our queries. 

Why Indexes Aren't Used

The presence of an index on a column does not guarantee it will be used. The following is a small list of factors that will prevent an index from being used.
  • The optimizer decides it would be more efficient not to use the index. If your query is returning the majority of the data in a table, then a full table scan is probably going to be the most efficient way to access the table.
  • You perform a function on the indexed column i.e. WHERE UPPER(name) = 'JONES'. The solution to this is to use a Function-Based Index.
  • You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
  • You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
  • You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used. Index Skip Scanning in Oracle 9i and above allow indexes to be used even when the leading edge is not referenced.
  • The use of 'OR' statements confuses older versions of the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. The only way of guaranteeing the use of indexes in these situations is to use an INDEX hint.
Now discussing all these in details with examples.


1. When using OR in a Bitmap index

SQL> SELECT * FROM EMP3
  2  WHERE EMPNO = 7900
  3  OR EMPNO = 7902;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO HI
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        900                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

Execution Plan
----------------------------------------------------------
Plan hash value: 2425169977

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    76 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP3 |     2 |    76 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=7900 OR "EMPNO"=7902)

However When Using OR in Normal Index: Index will be used:  - IN is same as OR. 

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7900 JAMES      CLERK            900
      7902 FORD       ANALYST         3000


Execution Plan
----------------------------------------------------------
Plan hash value: 3445868034

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     2 |    42 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP4    |     2 |    42 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP4_PK |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

2. When we use Arithmetic calculation. Then Normal Index will also not be used

SQL> SELECT * FROM EMP4 WHERE EMPNO+130=7499;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK           1400

Execution Plan
----------------------------------------------------------
Plan hash value: 498553951

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    21 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP4 |     1 |    21 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

For these situation we need to create function based indexes on EMPNO+130.

3. When using NOT in WHERE condition, Index will not be used

SQL> SELECT * FROM EMP6
  2  WHERE EMPNO NOT IN (7369,7499,7521,7566,7654);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        900                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2797479459

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     9 |   783 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP6 |     9 |   783 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

4. When using != , <> operators, Index will not be used

SQL> SELECT * FROM EMP6
  2  WHERE EMPNO <> 7698;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        900                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

13 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2797479459

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    13 |  1131 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP6 |    13 |  1131 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

5. When using IS NULL or IS NOT NULL. Index will not be used - FULL TABLE SCAN
SQL> SELECT * FROM EMP6 WHERE RANK IS NULL;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       RANK
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        900                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2797479459

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    11 |  1100 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP6 |    11 |  1100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RANK" IS NULL)

   
SQL> SELECT * FROM EMP6 WHERE RANK IS NOT NULL;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       RANK
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10          1
      7839 KING       PRESIDENT            17-NOV-81       5000                    10          1
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          1

Execution Plan
----------------------------------------------------------
Plan hash value: 1370987953

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |   300 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP6    |     3 |   300 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | RANK6_I |    14 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

6. Using OR clause and having another condition in Where clause


A). Using non-indexed column in condition:
SQL> SELECT * FROM EMP6
  2  where sal>2000
  3  OR job='CLERK';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       RANK
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10          1
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10          1
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        900                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          1

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2797479459

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |  1000 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP6 |    10 |  1000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">2000 OR "JOB"='CLERK')
   
B). Using Indexes column in condition - DEPTNO
SQL> SELECT * FROM EMP6
  2  where sal>2000
  3  or deptno = 10;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO       RANK
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10          1
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10          1
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          1

7 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2797479459

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   700 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP6 |     7 |   700 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">2000 OR "DEPTNO"=10)

 The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length. Don't worry in this case.


0 comments:

You Might Also Like

Related Posts with Thumbnails

Pages