Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Why would a view take 30X longer to run than the select clause it is composed of?
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???
the explain plan for the first one is
SELECT STATEMENT Cost = 11401 FILTER Cost = 1 NESTED LOOPS Cost = 1 NESTED LOOPS Cost = 1 HASH JOIN Cost = 1 PARALLEL_TO_SERIAL TABLE ACCESS TD_CIMS_RIDER_CONTRACT FULL Cost = 1 PARALLEL_TO_PARALLEL REMOTE Cost = 2 PARALLEL_FROM_SERIAL REMOTE Cost = 2 SERIAL_FROM_REMOTE REMOTE Cost = 2 SERIAL_FROM_REMOTE SORT AGGREGATE Cost = 2 NESTED LOOPS Cost = 1 NESTED LOOPS Cost = 1 TABLE ACCESS TD_BILLING_ACCT BY INDEX ROWID Cost = 1 INDEX XAK1TD_BILLING_ACCT RANGE SCAN Cost = 1 INDEX XPKTF_SERVICE_POINT_BILLING RANGE SCAN Cost = 2 INDEX XPKTD_SERVICE_POINT UNIQUE SCAN Cost = 2
the explain plan for the second one is different should it not be identical? look at cost statement it is 30 times bigger. Why did parallell work with first but not second??
SELECT STATEMENT Cost = 419210 FILTER Cost = 1 NESTED LOOPS OUTER Cost = 1 VIEW V_RPT_RATE_RIDERS Cost = 1 FILTER Cost = 1 NESTED LOOPS Cost = 1 MERGE JOIN Cost = 1 SORT JOIN Cost = 1 REMOTE Cost = 1 SERIAL_FROM_REMOTE SORT JOIN Cost = 2 REMOTE Cost = 1 SERIAL_FROM_REMOTE REMOTE Cost = 2 SERIAL_FROM_REMOTE SORT AGGREGATE Cost = 2 NESTED LOOPS Cost = 1 NESTED LOOPS Cost = 1 TABLE ACCESS TD_BILLING_ACCT BY INDEX ROWID Cost = 1 INDEX XAK1TD_BILLING_ACCT RANGE SCAN Cost = 1 INDEX XPKTF_SERVICE_POINT_BILLING RANGE SCAN Cost = 2 INDEX XPKTD_SERVICE_POINT UNIQUE SCAN Cost = 2 TABLE ACCESS TD_CIMS_RIDER_CONTRACT BY INDEX ROWID Cost = 2 INDEX XPKTD_CIMS_RIDER_CONTRACT RANGE SCAN Cost = 1Received on Wed Jan 26 2000 - 08:34:03 CST
![]() |
![]() |