Curious Construct
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 23 Nov 2006 14:30:24 +0000
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:
