RE: Optimizer wonk sought

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 24 Apr 2008 16:59:24 -0400
Message-ID: <1c7101c8a64e$14286270$1100a8c0@rsiz.com>

Based on 1266 being close enough to 1245 to not worry about the efficacy of the joins in reducing the result set size, I believe you should focus on getting those 1266 rows as cheaply as possible. Using an inline view should achieve that nicely, projecting the function result so that even if there is some dysfunction in the join resolution so that it is referenced many times at least it will be a value in hand rather than a context switch forcing function call. One thing you don't say is whether the sysdate value or the other criteria are responsible for the "smallness" of the 1266 result set size. That is, if you've got 144,000-ish rows to consider and the "criteria about the view are met" reduces the number to near the 1266, that is a reason to double nest the in-line view to prevent execution of the function on rows excluded by other criteria.

So

select some_tables.some_columns, a.ftime, a.other_columns   from some_tables,

       (select column_with_function_result ftime, other_columns
           from a_view_with_stored_function
           where column_with_function_result > sysdate
             and some_other_criteria_about_the_view_are_met) a

  where some_joins_on_the_tables_are_true

is potentially more expensive than

select some_tables.some_columns, c.ftime, c.other_columns

   from some_tables,

        (select
                column_with_function_result ftime,
                other_columns
         from   a_view_with_stored_function
         where pk in 
              (select a.pk from
                     (select rownum a_rownum,
                      pk 
                      from a_view_with_stored_function
                      where some_other_criteria_about_the_view_are_met) a
                      ) b
        )c

   where some_joins_on_the_tables_are_true

However, the other thing you don't say is the underlying structure of the view. That in and of itself could be projecting the function value many unneeded times before the "other criteria" and the sysdate restriction get you down to the 1266 rows. You could apply the same pattern above to an alternate construction of the view to avoid unneeded projection of the function value if that is the case. Of course if "other criteria" also reference the function, this won't help. A gratuitous order by pk in "a" might marginally improve the performance of the likely fast full scan on pk to select rows to project in "c". Using a view that does not call the function and only using the function inline on the component values of the selected tuples (except for the sysdate part) might also prove useful.

Good luck!

mwf                

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mary Elizabeth McNeely
Sent: Thursday, April 24, 2008 1:37 AM
To: oracle-l_at_freelists.org
Subject: Optimizer wonk sought

Can some brave optimizer wonk interested in a challenge please help me see what I'm missing here?

Terms of reading further: Only read further with the kindest of intent. No laughing at the blonde DBA. No taunting the blonde DBA about all the silly things she tried to do to fix this.

This is a long drawn out explanation; excuse typos in my psuedocode if there are any (due to the nature of my situation, I have to stay with psuedocode):

I have a query that currently works like this, which calls a view, and the view has a stored function populating one of the columns returned:

select some_tables.some_columns,
          a_view_with_stored_function.column_with_function_result,
          a_view_with_stored_function.other_columns
from  some_tables,
        a_view_with_stored_function
where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true and 
          a_view_with_stored_function.function_result > sysdate;

The stored function is executing many more times (144,000) than number of rows that would be returned by the view alone (1266), and also more than the number of rows returned by the view when joined with the rest of the query (1245) (for brevity, I'll call that "too darned often"). What I want it to do instead is execute the stored function only against the candidate rows that are returned from the already-completed view-table join. (I have done my homework and am certain this is the more efficient way to do it.)

My problem is, I can't convince Oracle to follow the execution path I want.

Here's what I've tried:
<snip>

--
http://www.freelists.org/webpage/oracle-l




application/ms-tnef attachment: winmail.dat

Received on Thu Apr 24 2008 - 15:59:24 CDT