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!

How to force Oracle to use an index ?

user639304Jan 18 2012 — edited Jan 18 2012
Hi All,

I'm learning tuning of SQL statements.

I'm using SQL*Plus, and I'm connected to Oracle as User1, and this User1 has a table named emp which has the same rows as the scott.emp table.
I've created an index (named index_emp_ename) on the ename column of my emp table, and I want to tell Oracle to use this index
for the query select ename from emp , but Oracle uses full table scan (though I've gathered statistics). Please, you can see below what I've done to tell me where 'm wrong :
SQL> create index index_emp_ename on emp(ename);

Index created.

SQL> analyze index index_emp_ename validate structure;

Index analyzed.

SQL>   select name, btree_space, lf_rows, height
  2    from index_stats;

NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
INDEX_EMP_ENAME                       8000         14          1

SQL> show user
USER is "USER1"
SQL> exec dbms_stats.gather_table_stats(ownname =>'USER1', tabname =>'EMP');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(ownname =>'USER1', indname =>'index_emp_
ename');

PL/SQL procedure successfully completed.

SQL> explain plan for select /*+ index( EMP INDEX_EMP_ENAME) */ ename from emp;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 3956160932

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

8 rows selected.

SQL>
Thanks in advance .
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 15 2012
Added on Jan 18 2012
4 comments
7,054 views