Re: Union all very slow.

Randy,
Try using the 'push_pred' hint to see if it does make any difference.

rgds

On 10/26/06, Steiner, Randy <Randy.Steiner_at_nyct.com> wrote:
>
> 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_at_gmail.com]
> > Sent: Wednesday, October 25, 2006 6:37 PM
> > To: Steiner, Randy
> > Cc: Dennis Williams; oracle-l_at_freelists.org
> > 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_at_nyct.com> 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_at_gmail.com
> > <mailto:oracledba.williams_at_gmail.com> ]
> > > Sent: Wednesday, October 25, 2006 2:40 PM
> > > To: Steiner, Randy
> > > Cc: oracle-l_at_freelists.org
> > > 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