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:


TRY 2 - take the stored function out of the view and put it directly into the 
main query
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select some_tables.some_columns,
          stored_function.function_result(columns_returned_from_the_view) 
result_column_alias,
          a_view_with_stored_function.other_columns
from  some_tables,
        a_view_without_stored_function
where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true and 
          result_column_alias > sysdate;

Result: Won't parse - Oracle's mad that I have tried to use the 
result_column_alias in the where statement.


TRY 3 - take the stored function out of the view and put it directly into the 
main query and where clause of main query
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select some_tables.some_columns,
          stored_function.function_result(columns_returned_from_the_view) 
result_column_alias,
          a_view_with_stored_function.other_columns
from  some_tables,
        a_view_without_stored_function
where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true and 
          stored_function.function_result(columns_returned_from_the_view) > 
sysdate;

Result: Function still executes "too darned often".  


TRY 4 - take the stored function out of the view and put it directly into the 
callling query and where clause of calling query, but in a subquery
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select query_alias.result_column_alias, query_alias.other_columns from
(
select some_tables.some_columns,
          a_view_with_stored_function.column_with_function_result 
result_column_alias,
          a_view_with_stored_function.other_columns
from  some_tables,
        a_view_without_stored_function
where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true
) query_alias
where 
          query_alias.result_column_alias> sysdate;

Result: ERROR at line 67:
ORA-01843: not a valid month

I'm feeding it the same columns it was getting from the view, not sure what's 
up with that, but abandoning this case for now, especially based on results I 
saw in later cases ... this approach probably wouldn't have worked anyway.  


TRY 5 - take the stored function out of the view and also (I had planned/hoped) 
externalize it from the table-view join activity 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select query_alias.result_column_alias, query_alias.other_columns from
(
select some_tables.some_columns,
          stored_function.function_result(columns_returned_from_query_alias1) 
result_column_alias,
          query_alias2.other_columns
from
    (
   select some_tables.some_columns
   from  some_tables,
           a_view_without_stored_function
   where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true
    ) query_alias1  
) query_alias2
where 
          query_alias2.result_column_alias > sysdate;

Result: Function still executes "too darned often".  


TRY 6 - bang head against the desk, then decompose the TRY 5 query to learn 
more about it, tracing during each step
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
TRY 6A:

   select some_tables.some_columns
   from  some_tables,
           a_view_without_stored_function
   where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true
-- innermost query alone returns 1245 rows - the function isn't called in this 
query - just trying to prove the function doesn't execute during 
a_view_without_stored_function without my realizing it, causing part of the 
problem - trace files prove the function isn't executing here

TRY 6B:
select some_tables.some_columns,
          stored_function.function_result(columns_returned_from_query_alias1) 
result_column_alias,
          query_alias2.other_columns
from
    (
   select some_tables.some_columns
   from  some_tables,
           a_view_without_stored_function
   where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true
    ) query_alias1  
) 
-- add the stored function in: innermost query and next outer query alone 
returns 1245 rows, and executes the stored function about 1266 times - this is 
about the number of times I want the function to execute, so far, so good

TRY 6C:
Add the outermost query back in, but not the outermost where clause from TRY 5:
(
select some_tables.some_columns,
          stored_function.function_result(columns_returned_from_query_alias1) 
result_column_alias,
          query_alias2.other_columns
from
    (
   select some_tables.some_columns
   from  some_tables,
           a_view_without_stored_function
   where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true
    ) query_alias1  
) query_alias2
-- still returns 1245 rows, and executes the stored function about 1266 times - 
so far, still so good, and I'm getting the CPU, I/O count, and run-time gains 
I'd hoped for.  The only thing left to do is add the where clause back in.  

-- put the outermost where clause back 
select query_alias2.result_column_alias, query_alias2.other_columns from
(
select some_tables.some_columns,
          stored_function.function_result(columns_returned_from_query_alias1) 
result_column_alias,
          query_alias2.other_columns
from
    (
   select some_tables.some_columns
   from  some_tables,
           a_view_without_stored_function
   where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true
    ) query_alias1  
) query_alias2
where 
          query_alias2.result_column_alias > sysdate;
-- still returns 1245 rows, but the wheels fall off at this point - we're back 
to function still executing "too darned often" (144000 times)  

SO, THE OPTIMIZER IS BEING "HELPFUL", TRYING TO APPLY THE OUTERMOST WHERE 
PREDICATE DURING THE EXECUTION OF THE INNER QUERIES, SOMEHOW HAVING REWRITTEN 
THEM IN A "SMARTER" FASHION.  Not really helpful - uses twice the I/O, twice 
the CPU, and twice the run time of the query without the outermost where 
clause.  


Other things I tried:

TRY 7 - externalizing the query into another view (so now there are two layers 
of views)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

create view whywhywhy_dammit as 
select query_alias2.result_column_alias, query_alias2.other_columns from
(
select some_tables.some_columns,
          stored_function.function_result(columns_returned_from_query_alias1) 
result_column_alias,
          query_alias1.other_columns
from
    (
   select some_tables.some_columns,
             query_alias2.result result_column_alias,
            query_alias2.other_columns
   from  some_tables,
           a_view_without_stored_function
   where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true
    ) query_alias1  
) query_alias2;

select some_columns, result_column_alias
from whywhywhy_dammit 
where result_column_alias > sysdate;

Result: Function still executes "too darned often" - the optimizer continues to 
be HELPFUL 


TRY 8 - externalizing the two "internal" queries from TRY 5 into a WITH clause
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Result: Function still executes "too darned often" - the optimizer continues to 
be HELPFUL 


TRY 9 - getting desperate, add NO_MERGE hint to TRY 5
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Result: Function executes 2525 times - not the 1266 I wanted, but much better, 
and I/O is lower, but bizarre execution plan takes a long time to execute and 
lots of CPU time, both worse than the original query


TRY 9 - getting more desperate, add PUSH_SUBQ hint to TRY 5, even though I'm 
not sure what it does, but it has the word subquery in its help text, so it 
must be all good, right?????
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Result: Function executes 2525 times - not the 1266 I wanted, but much better, 
and I/O is lower, but same bizarre execution plan as TRY 9 takes a long time to 
execute and lots of CPU time, more than the original query


TRIES 10 and 11 - now completely thrashing and having nothing to lose (pride 
hours ago having evaporated), tried adding UNNEST and then NO_UNNEST hints 
added to TRY 5
+++++++++++++++++++++++++++++++++++++++++++++++++
Result: back to function still executing "too darned often", in both cases




TYPING IN ALL CAPS BECAUSE I'M NOW YELLING: ALL I WANT IS THE OPTIMIZER TO 
EXECUTE THIS USING THE PLAN IT USED IN TRY 6C:

select query_alias.result_column_alias, query_alias.other_columns from
(
select some_tables.some_columns,
          stored_function.function_result(columns_returned_from_query_alias1) 
result_column_alias,
          query_alias2.other_columns
from
    (
   select some_tables.some_columns
   from  some_tables,
           a_view_without_stored_function
   where some_criteria_about_the_view_are_met and
          some_joins_on_the_tables_are_true
    ) query_alias1  
) query_alias2


THEN DO THIS STEP, only seeing result_column_alias as an atomic value being 
returned from its child query, not an opportunity to perform slick optimizer 
techniques:
where 
          query_alias2.result_column_alias > sysdate;



HOW CAN I GET THERE FROM HERE?  What am I missing?  alter system set 
smart_alec_optimizer=off; ?



As always, thanks for whatever hints you can offer.  Let me know if you need 
more information - hope this is enough for you to get the concept even with 
just pseudocode.  


Mary Elizabeth
(not feeling very smart, with head sore from being banged on desk, client team 
members think she's crazy because she's taken to walking around the office 
muttering this afternoon)
--
http://www.freelists.org/webpage/oracle-l


Other related posts: