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 -> Re: Why would a view take 30X longer to run than the select clause it is composed of?

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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 26 Jan 2000 10:53:15 -0500
Message-ID: <j03u8s0269i90kreauiq599be2i7haflqt@4ax.com>


A copy of this was sent to juilous_at_hotmail.com (juilius) (if that email address didn't require changing) On 26 Jan 2000 08:34:03 -0600, you wrote:

>
>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
>
>

we cannot in general push a predicate down into a view with an outer join.

The outer join is not even needed in this case. you have:

( v_rpt_rate_riders.bill_acct_nbr = td_rider_contract.bill_acct_nbr (+) )

meaning that a record will be 'made up' in td_rider_contract if no such bill_cct_nbr exists HOWEVER later in the predicate you also say:

... and td_rider_contract.rider_cd = 'd1' )

Well, if the outer join was actually put to use, the td_rider_contract.rider_cd column will be NULL and will not be 'd1'.

The outer join is *not* needed in this query (above). It can only be making the query harder to process/ perhaps slower then it could be.

The query below is the same as:

select bill_acct_nbr, billing_period_end_dt_tm, rider_cd from (select /*+ parallel hint */ 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) 
      )

where (tariff_rate_typ = '600' and rider_cd = 'D1')

with the outer join in the inner query -- you cannot push predicates down in general (it can change the meaning of the query if you do). So, this built the entire inner query (in parallel perhaps) and then serially did the outer query against this temporary result set it stuck somewhere.

If you lose the outer join (which in your case, if you have a predicate on columns in the td_rider_contract you do not NEED an outer join) -- it'll be able to push the predicates down into the view

>
>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

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jan 26 2000 - 09:53:15 CST

Original text of this message

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