Index on foreign key is not picked in join queries

Conside开发者_Python百科r the EMP, DEPT example It is very common to query the EMP table in the context of a deptno. If you frequently query:

select * from dept, emp
 where emp.deptno = dept.deptno and dept.deptno = :X;

i have a Index deptNo_idx on employee table . but whenever i see the plan of execution this index is never used. even giving an index hint was not useful

any idea how to use the index on the foreign key column?

The EMP and DEPT tables are not particularly good tables to use when you're trying to understand optimizer behavior. They're so small that the optimizer will frequently correctly determine that full table scans are more efficient than index access. Additionally, there are relatively few distinct DEPTNO values so the optimizer knows that it's going to have to fetch a relatively large percentage of the rows from the EMP table. Since there are few rows in the tables and you need to fetch a large fraction of the rows for any given DEPTNO value, a table scan will be more efficient.

If the tables were larger and there were more departments, the optimizer would be more likely to determine that using the index would be more efficient.

A) Change the syntax to use a proper join:

select *
from dept
join emp on emp.deptno = dept.deptno
where dept.deptno = :X;

B) Define an index on dept.deptno, since that's the primary filter (the where clause)

CREATE INDEX DEPT_DEPTNO_IDX ON DEPT(DEPTNO);

That should make it fly.