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

Home -> Community -> Usenet -> c.d.o.server -> Why would a view take 30X longer to run than the select clause it is composed of?

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

From: juilius <juilous_at_hotmail.com>
Date: 26 Jan 2000 08:34:03 -0600
Message-ID: <gBDj4.2795$in3.23108@newscene.newscene.com>

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 = 1
Received on Wed Jan 26 2000 - 08:34:03 CST

Original text of this message

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