-
How can i force oracle not to do a full table scan on a query with an outer join?
-
Provide the query, the explain plan and the version of Oracle and I'll show you.
Basically, however, an outer join, by itself, does not mean that a table scan will be done. There are many ways to solve an outer join, and it will certainly use an index when it makes sense.
- Chris
-
Example: There are 2 indexes on standard scott's tables:
1) PK_DEPT on dept(deptno), 2)EMP_FK on emp(deptno)
select
dept.dname, emp.ename
from dept,emp
where dept.deptno = emp.deptno(+)
and dept.deptno = 40;
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=90)
NESTED LOOPS (OUTER) (Cost=2 Card=5 Bytes=90)
TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=11)
INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=14 Bytes=98)
INDEX (RANGE SCAN) OF 'EMP_FK' (NON-UNIQUE)
You see that outer join does not perform full scan, it uses index on EMP(deptno).
Note that the driving table is DEPT. In outer joins driving table is always
the table without (+) sign, so outer join can not use index on the driving table DEPT(deptno).
inosov
Brainbench MVP for Oracle DBA
-
Everything there was correct except that last statement. I suspect a typo . As you can see from the plan inosov provided - the index on dept_no on *both* tables was, indeed, used.
- Chris
-
In the example, the index on dept(deptno) was actually used due to
predicate 'dept.deptno=40' but not for outer join itself.
The main idea was that the index on dept(deptno) is useless for outer join execution,
because emp can not be chosen as driving table.
sorry for some inaccuracy.
inosov
Brainbench MVP for Oracle DBA
-
Ahhhhhhh,
Hadn't seen where you were going with that is all
Objection over-ruled... the verdict stands!
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|