Re: Performance problem with SQL query

Hi Todd, thanks for all your replies! I am on holiday for this week, but will definitely try all your ideas next week. Are these new methods you describe all available in Oracle8i? Also, there is little chance of me being able to alter optimizer_index_cost_adj. I would be worried about breaking queries elsewhere even if we had permission to get that done! Cheers,
Mark

Todd Barry <tbarry2000_at_hotmail.com> wrote: : 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

--