Re: Curious Construct

Niall

Yes, I've seen both

1) (9i at least) the optimizer often loses the will to live with SQL built on (excessively) complex views, and picks a duff plan. 

(BTW: who says a view used by just about every application component that depends on a nest of other (UNION ALL) views just to smoothe out a bonkers data model is a bad idea just because it needs about 70 data access steps? maybe I should write that one up for Oracle WTF)

2) inline selects can be used pretty much anywhere an expression can be (and will raise ORA-01427: single-row subquery returns more than one row if necessary). 9i SQL Guide 'Using Subqueries says: "Scalar subqueries, which return a single column value from a single row, are a valid form of expression. You can use [it] in most of the places where expr is called for in syntax".

We worked around in a similar way to you

Cheers Nigel

----- Original Message ----
From: Niall Litchfield <niall.litchfield@gmail.com>
To: oracle-l <oracle-l@freelists.org>
Sent: Thursday, November 23, 2006 2:30:24 PM
Subject: 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

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