Hi

I tried thw following query and saw that the query access fully emp table first then by rowid and unique scan in dept table.

select emp.job, dept.dname from emp,dept
where emp.deptno=dept.deptno
and emp.deptno=20
/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=108)
1 0 NESTED LOOPS (Cost=2 Card=2 Bytes=108)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 B
ytes=22)

3 2 INDEX (UNIQUE SCAN) OF 'DEPT_DEPTNO_PK' (UNIQUE)
4 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=5 Bytes=160)

I am wondering how to modify this query so it doesnt do a full table scan in table emp.

I had to trace a few SQL queries few days ago and was facing a very similar problem, I indexed one of columns, in this case would be emp.deptno (was referenced in where clause) and the excution plan was changed and didnt do full table scan anymore however with above query and indexing foreign key (emp.deptno) it still does full table scan any suggstions?

Cheers