Performance problem with SQL query

From: <Mark.Wright_at_bristol.ac.uk>
Date: Fri, 5 Sep 2003 15:29:35 GMT
Message-ID: <HKqz1B.J29@bath.ac.uk>

Hi all,
We a serious performance problem a SQL construct that we are using. We are running Oracle 8.1.7 on SunOS 5.6.

The following two queries run just great, as shown in the 'Elapsed' times. The result of the first query has been entered explicitly into the second query. The explain plans are nice:

function_x returns a SQL type, id_tab, that is a table of VARCHAR2(100).

SELECT *
FROM THE(

        SELECT  CAST(
                function_x()
                AS id_tab)
        FROM    dual);

Elapsed: 00:00:00.08
example id='1000000000018028570'

QUERY PLAN
SELECT STATEMENT CHOOSE Cost =

  COLLECTION ITERATOR PICKLER FETCH
    TABLE ACCESS FULL DUAL

---

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

AND     id IN 
        ('[id]',
        20 odd lines of string [id], as given by first function


        );

Elapsed: 00:00:00.37

QUERY PLAN
SELECT STATEMENT CHOOSE Cost = 7

  SORT AGGREGATE

    PARTITION RANGE  SINGLE      KEY KEY
      INLIST ITERATOR
        TABLE ACCESS  BY LOCAL INDEX ROWID  table_x  ANALYZED  KEY KEY
          INDEX  RANGE SCAN  INDX_table_x  ANALYZED  KEY KEY

-------------------------------------------------------------------

In the query that is causing the problems, the first query is a subquery of the second. This should be fine, and is recommended by asktom.oracle.com, but causes the total query to take 10x longer than the sum of the two seperately.

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)


        );

Elapsed: 00:00:04.06

QUERY PLAN
SELECT STATEMENT CHOOSE Cost = 1535

  SORT AGGREGATE
    HASH JOIN

      PARTITION RANGE  SINGLE      KEY KEY
        TABLE ACCESS  FULL  table_x  ANALYZED  KEY KEY
      VIEW    VW_NSO_1
        SORT  UNIQUE
          COLLECTION ITERATOR  PICKLER FETCH
            TABLE ACCESS  FULL  DUAL

--------------------------------------------

The reason is easy to see: partition range-scans. table_x is huge, and partitioned. A partition typically has 1.5M rows. Why is inserting the CAST function causing the query to not use the index? This method was recommended all over the place to solve the old "dynamic 'IN' list" problem. We used to solve this by building up dynamic SQL with a vast 'IN' list, but it was very ugly and we maxed out the 32KB size of a VARCHAR2 for use in the dynamic SQL anyway. My only suspicion is that the CAST is causing Oracle to think that an implicit type conversion to VARCHAR2 is required, which prevents the index being used. If this is so, how can we prevent this?
Any help would be appreciated. Cheers,
Mark

-- 

Received on Fri Sep 05 2003 - 10:29:35 CDT