RE: Union all very slow.

Here is my view:

CREATE  OR REPLACE VIEW sales_adjusted_v
as
SELECT 
 D_TRANSACTION_DATE_TIME
D_transaction_date_time
,V_CARD_NO                                                V_card_no

,N_TRANSACTION_AMT
N_transaction_amt
,D_SETTLEMENT_DATE
D_settlement_date
,V_TRACE_NO                                               V_trace_no

FROM sales_t
UNION all
SELECT
 d_sale_date
D_transaction_date_time
,v_card_no                                                V_card_no 
,n_transaction_amt
N_transaction_amt                       
,d_refund_date
D_settlement_date                     
,v_original_trace_no                                      V_trace_no 
FROM ADJUSTMENT_DETAILS_V
;

If I query either portion on its own, the appropriate index is used, but
not on the full view.

Select * from sales_adjusted_v where v_card_no = '123';


The one point I need to make is that v_card_no is encrypted via TDE.

Thanks
Randy


> -----Original Message-----
> From: Ghassan Salem [mailto:salem.ghassan@xxxxxxxxx]
> Sent: Wednesday, October 25, 2006 6:37 PM
> To: Steiner, Randy
> Cc: Dennis Williams; oracle-l@xxxxxxxxxxxxx
> Subject: Re: Union all very slow.
> 
> Randy,
> can you give the full query (using the view)?
> when you comment one part of the union all, the system is probably
doing a
> push-predicate
> into the view, while it's not doing this when you use the UA.
> 
> rgds
> 
> 
> On 10/25/06, Steiner, Randy <Randy.Steiner@xxxxxxxx> wrote:
> 
>       I am  using 10g.
> 
>       The difference in the execution plan is:
>       This is by itself:
>                INDEX* (RANGE SCAN) OF 'XP_DBCR_CARD_NO' (INDEX) (Co
:Q1000
>                 st=1 Card=3 Bytes=75)
> 
>       and this is when it is part of the union ALL:
> 
>              INDEX* (FAST FULL SCAN) OF 'XP_DBCR_CARD_NO' (IN :Q1000
>                 DEX) (Cost=3251 Card=154734 Bytes=3868350)
> 
> 
>       > -----Original Message-----
>       > From: Dennis Williams [mailto: oracledba.williams@xxxxxxxxx
> <mailto:oracledba.williams@xxxxxxxxx> ]
>       > Sent: Wednesday, October 25, 2006 2:40 PM
>       > To: Steiner, Randy
>       > Cc: oracle-l@xxxxxxxxxxxxx
>       > Subject: Re: Union all very slow.
>       >
>       > Randy,
>       >
>       > I'd start by doing an EXPLAIN PLAN on each part and the whole
> thing.
>       You
>       > didn't mention your Oracle version, but maybe the CBO is
getting
> smart
>       > enough to optimize the entire query, so is making a bad
decision.
>       >
>       > Dennis Williams
>       >
>       >
>       --
>       http://www.freelists.org/webpage/oracle-l
> 
> 
> 
> 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: