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.
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.
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:
Post a Comment