Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using a dummy where-clause to force the use of an index

Peter MarcoenOct 17 2012 — edited Oct 17 2012
I notice that an index only gets used when I use the index key in the where clause. Should I use a dummy where clause to force the index to be used?

SQL> create table emp (
2 empno NUMBER(5),
3 empname VARCHAR2(15) );

Table created.

SQL> create index idx_emp_no on emp (empno);

Index created.

SQL> insert into emp (empno, empname) values (1, 'Peter');

1 row created.

SQL> set autotrace traceonly;
SQL> select empno from emp;


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

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

SQL> select empno from emp where empno > 0;


Execution Plan
----------------------------------------------------------
Plan hash value: 434430053

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_EMP_NO | 1 | 13 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

As you can see, using a dummy where clause (empno > 0), I manage to reduce the cost from 3 to 1.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 14 2012
Added on Oct 17 2012
17 comments
2,668 views