RE: SQL statement with hints or without hints (LONG)

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Fri, 12 Apr 2002 11:28:29 -0800
Message-ID: <F001.00443800.20020412112829@fatcity.com>

> 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Apr 12 2002 - 14:28:29 CDT