Re: Performance problem with SQL query

From: <Mark.Wright_at_bristol.ac.uk>
Date: Mon, 27 Oct 2003 11:41:11 GMT
Message-ID: <HnEz4n.MBF@bath.ac.uk>

Just in case anyone remembers and was interested; regarding newsgroup post HKqz1B.J29_at_bath.ac.uk, Date 05-Sep-03, Title "Performance problem with SQL query", author Mark.Wright_at_bristol.ac.uk; concerning terrible performance (indexes not being used correctly) of queries of the form:

SELECT SUM(NVL(column,0))
FROM table_x
WHERE options blah

AND     id IN (
        SELECT  *
        FROM    THE(
                SELECT  CAST(
                        function_x()
                        AS id_tab)
                FROM    dual)
        );

This it turns out is now an unpublished bug/"feature" of Oracle versions below 9.2.0.3. From:
http://metalink.oracle.com/metalink/plsql/ml2_gui.startup

Bug 2860984 Bad plan possible with collection iterator (table/Cast) This note gives a brief overview of bug 2860984. Affects:

    Product (Component)	Oracle Server (RDBMS)
    Range of versions believed to be affected	Versions < 10G

Versions confirmed as being affected

9.2.0.3

Platforms affected Generic (all / most platforms affected)

Fixed:

This issue is fixed in

9.2.0.4 (Server Patch Set)
10G Production Base Release

Symptoms:

Performance of Query/ies affected

Related To:

Optimizer
Datatypes - Objects (Types/Collections)

Description

Unnesting of a subquery with a collection iterator may unnecessarily impose a join order which is inefficient resulting in a poor query time.

Needless to day, this is a major pain for us on 8i... Mark

--

Received on Mon Oct 27 2003 - 05:41:11 CST