Re: Union all very slow.

  • From: "Ghassan Salem" <salem.ghassan@xxxxxxxxx>
  • To: Randy.Steiner@xxxxxxxx
  • Date: Thu, 26 Oct 2006 00:36:42 +0200

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]
> 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
>
>
--
//www.freelists.org/webpage/oracle-l



Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2006