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 .