|
> Using RBO is unnecessary if you are
using Oracle8 v8.0 or above. The CBO
> outperforms RBO in any situation except queries against the data dictionary > (because you cannot analyze the data dictionary). Never
say never and Never say always.
I have
found the above statement to be true except in one case, and that involves a bug
that was introduced somewhere in the 8.1.5 tree and _almost_ fixed in
8.1.7.1. It is particularly nasty in the 8.1.6 tree and it appears to
be fixed in 9.0.1.2. I don't have the bug #, but the situation is
follows:
1) You are joining multiple large tables
together
-- The more & larger the
tables you are joining, the worse the effects
2) One or more of the join columns is in the SELECT
list
3) You are ordering by > 1 of the join
columns.
-- this can be an ORDER BY, GROUP BY or
DISTINCT clause or even a UNION, INTERSECT or MINUS! Anything that causes a sort
to occur on the join column
The
CBO will choose to do SORT/MERGE joins (with full table scans) when any other
join method is more efficient.
Through normal hinting you CAN NOT get the CBO to use nested loops
with index range scans (that's part of the bug). If you specify the INDEX_ASC()
and USE_NL() hints, the CBO will do an FAST FULL SCAN on the index on the
specified index.
If you
move the unsorted query into an inline view and sort outside the inline view,
you can get a near-optimal execution path -- and hinting works properly.
However, if you just use the /*+ RULE */ hint, you will get better
performance than with the inline view method.
How do
you determine if you are running into this bug? There are several ways, but
the best way is to run your query without your "sort" operation. If the
query runs significantly faster without the sort than with, you may be hitting
this bug.
An
example:
SELECT e.empno, e.deptid, dept.name,
d.dependent_name
FROM emp e, dept, dependent
d
WHERE e.deptid = dept.id
AND emp.empno = d.empno
ORDER BY e.empno, e.deptid;
Inline
view method
SELECT /*+ NO_MERGE(x) */ *
FROM (
SELECT e.empno, e.deptid, dept.name,
d.dependent_name
FROM emp e, dept,
dependent d
WHERE e.deptid =
dept.id
AND emp.empno = d.empno)
x
ORDER BY empno, deptid;
Rule Hint:
SELECT /*+ RULE */ e.empno, e.deptid, dept.name,
d.dependent_name
FROM emp e, dept, dependent
d
WHERE e.deptid = dept.id
AND emp.empno = d.empno
ORDER BY e.empno, e.deptid;
Even with this bug around, I would still
highly recommend the CBO over the RBO. You just have to know the
exceptions.
Caver |
- Re: SQL statement with hints or without hints (LONG) Toepke, Kevin M
