Re: Performance problem with SQL query

Hi Mark:

This is probably not related to the performance issue, but the cast of the collection result can now be done as:

select sum(nvl(column,0))
  from table_x
 where ...
   and id in (select *

                from table(cast(function_x() as id_tab)));

No more confusing THE and DUAL references.

If the ID column is not a VARCHAR2, you could:

   and id in (select to_number(column_value)

                from table(cast(function_x() as id_tab)));

Also, does a /*+ first_rows */ hint change the plan at all?

What about modifying the optimizer_index_cost_adj value (alter session set optimizer_index_cost_adj = 10 -- default is 100)?

-Todd

>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
Received on Fri Sep 05 2003 - 14:11:34 CDT