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