Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Curious Construct

Re: Curious Construct

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 24 Nov 2006 11:13:43 +0000
Message-ID: <7765c8970611240313v27832b06ld9709d0179c48409@mail.gmail.com>


Thanks to all who have offered comments, and the generic name scalar subquery which aids researching this a lot.

On 11/23/06, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
>
> 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

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

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 24 2006 - 05:13:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US