Curious Construct

Our apps implementation has hit a curious SQL construct that seems to be
causing the CBO all sorts of interesting problems in optimization. The
problematic SQL is

select
   po_line_id
,   rate_basis
,   rate_basis_dsp
,   asg_rate_type
,   price_type_dsp
,   rate_value
,   currency_code
,   currency_name
,   start_date
,   end_date
from
   po_temp_labor_rates_v
where
 po_line_id =
     nvl(
             (
             select
                 stepvalues.number_value
             from
                 hr_api_transactions txn
             ,   HR_API_TRANSACTION_STEPS steps
             ,   hr_api_transaction_values stepvalues
             where
                 txn.item_type = :1
             and txn.item_key = :2
             and steps.transaction_id = txn.transaction_id
             and steps.api_name = :3
             and stepvalues.transaction_step_id = steps.transaction_step_id
             and stepvalues.name = :4
             )
       ,     (
             select
                 po_line_id
             from
                 per_all_assignments
             where
                 assignment_id = :5
             )
         );

PO_TEMP_LABOR_RATES_V being a complex view.

For a start I didn't know you could even do

select ...
from ...
where col = nvl(<select statement 1>,<select statement 2>);

we've replaced this (while we wait for a resolution from oracle on the bug
they have logged) with

select ...
from
where col  = OUR_NEW_FUNCTION(param_list) where the parameters are the bind
variables originally supplied where our new function is a replacement for
NVL that basically evaluates the first cursor and if nothing found evaluates
the second. I guess my purpose here is twofold

1. anyone else seen the NVL(select,select) construct?
2. what if anything did you do to rewrite.



--
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: