Re: Why would a view take 30X longer to run than the select clause it is composed of?

juilius wrote in message ...
>
>We need to make a select clause a view (it is being used by 3rd party
>software)
>
>We have the following statement
>
>select /*+ parallel (td_cims_rider_contract, 10) */
>v_rpt_rate_riders.bill_acct_nbr, v_rpt_rate_riders.billing_period_end_dt_tm
>td_rider_contract.rider_cd
>from
>td_rider_contract td_cims_rider_contract,
>v_rpt_rate_riders v_rpt_rate_riders
>where
>(v_rpt_rate_riders.bill_acct_nbr=td_rider_contract.bill_acct_nbr (+) )
>and (billing_period_end_dt_tm between rider_eff_dt and rider_expn_dt)
>and (v_rpt_rate_riders.tariff_rate_typ = '600' and
>td_rider_contract.rider_cd = 'd1')
>
>it takes 55 secs to return result
>
>
>
>if i do the following
>
>create or replace view vf_test as
>select /*+ parallel (td_cims_rider_contract, 10) */
>v_rpt_rate_riders.bill_acct_nbr, v_rpt_rate_riders.billing_period_end_dt_tm
>td_rider_contract.rider_cd
>from
>td_rider_contract ,
>v_rpt_rate_riders
>where
>(v_rpt_rate_riders.bill_acct_nbr=td_rider_contract.bill_acct_nbr (+) )
>and
>(billing_period_end_dt_tm between rider_eff_dt and rider_expn_dt)
>
>and then do
>
>select bill_acct_nbr, billing_period_end_dt_tm, rider_cd
>from
>vf_test
>where
> (tariff_rate_typ = '600' and rider_cd = 'D1')
>
>this takes 31 minutes to run. why???
>

[ explain plan's snipped ]

I agree with Thomas's reply. For further information and more examples of why Oracle cannot in general optimise the outer join on a view, see my web page:

   http://home.clara.net/dwotton/dba/ojoin2.htm

Dave.
--
Remove "nospam" from my address to reply by email Received on Thu Jan 27 2000 - 01:30:50 CST