optimize join

[pando]

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

[bberg@rtnyc.com]

if the table has few rows (less than 5000) Oracle may scan anyway.

But...
If you do NOT index the column emp.deptno then it will scan as well.
Even with an index it may scan. Try using a hint /*+ INDEX(EMP DEPTNO *) to force it. Be sure to check tkprof to be sure you aren't worsening your code.

sometimes a full scan is not too bad.

p.s. Normal b-tree index is not too helpful if there are only a few distinct departments, try a bitmapped one possibly.

I don't know your code or rows sizes to say more.

[jmodic]

What bberg has written is totaly correct.

I just want to add one thing. Pando, you are reading your explain plan wrong. In fact, what is happening during the execution of the query is:

Step1: Index DEPT_DEPTNO_PK is searched for a unique value of DEPTNO=2
Step2: The corresponding row from table DEPT is read directly by rowid obtained in step 1
Step3: Table EMP is full-scaned to find matching rows to the DEPT row found in step 2.

In this case the so-called driving table is table DEPT. In case you were looking for more than one department, then a RANGE SCAN would be performed on index in step 1, and for each row obtained from that step it would repeat steps 2 and 3.

As allready mentioned, you could persuade the optimizer to use index on EMP. Either suggest this with proper hint in a query (/*+INDEX*/ or /*+RULE*/ will do it) or change optimizer mode to RULE (either on the system or session level) or delete statistics on both tables involved (thus implicitly forcing RULE mode). Hovewer it is almost certain that the response time will be worse if accessing the EMP table via index in this case where whole table is stored in a couple of blocks that can be read in a single I/O operation.

This is one example where cost based optimizer is probably chosing more clever execution plan than the one user is trying to achive. This, of course, is not allways the case, but with each version Oracle optimizer is becoming more "clever" and reliable, provided that you feed him with accurate statistics.

HTH,