CODEWITHSUNDEEP
sqloracle-databaseindexing
Pravin Satav
Pravin Satav

Reputation: 702

Index Hint is not working

I m trying to learn about index hint, but even after providing hint in query its not been used. Here's steps I followed, am I missing something?

SQL> desc emp_1;
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMP_ID   NUMBER                                 
EMP_NAME VARCHAR2(20) Y                         
DEPTNO   NUMBER(10)   Y               


SQL> desc dept
Name      Type         Nullable Default Comments 
--------- ------------ -------- ------- -------- 
DEPT_ID   NUMBER       Y                         
DEPT_NAME VARCHAR2(20) Y                         


SQL> CREATE INDEX abc_idex ON emp_1 (deptno);

Index created

select  /*+ index(emp_1.abc_idex) */ emp_name from emp_1
INNER JOIN dept ON  emp_1.deptno = dept.dept_id

Explain Plan :- 

SELECT STATEMENT, GOAL = ALL_ROWS           6   1   28
 HASH JOIN          6   1   28
  TABLE ACCESS FULL EXAMINBI    EMP_1   2   1   25
  TABLE ACCESS FULL EXAMINBI    DEPT    3   4   12

As per Rene's answer

select  /*+ index(emp_1 abc_idex) */ emp_name from emp_1
INNER JOIN dept ON  emp_1.deptno = dept.dept_id

Explain Plan 

SELECT STATEMENT, GOAL = ALL_ROWS           271 100000  800000
 MERGE JOIN         271 100000  800000
  TABLE ACCESS BY INDEX ROWID   EXAMINBI    EMP_1   267 100000  500000
   INDEX FULL SCAN  EXAMINBI    ABC_IDEX    131 100000  
  SORT JOIN         4   4   12
   TABLE ACCESS FULL    EXAMINBI    DEPT    3   4   12

Upvotes: 0

Views: 3597

Answers (1)

René Nyffenegger
René Nyffenegger

Reputation: 40499

According to Tahitis documentation, you must not put a . between the table spec and the index spec.

Upvotes: 3

Related Questions