RE: Union all very slow.

Hmm...how do the cardinality numbers in the plan reflect reality? Are they significantly off? If so, a cardinality hint may get you going in the right direction.

Not much to go on here, so, it's just a guess....

-Mark

--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

There is nothing so useless as doing efficiently that which shouldn't be
done at all.  -Peter F. Drucker, 1909-2005


-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Steiner, Randy
Sent: Wednesday, October 25, 2006 2:49 PM
To: Dennis Williams
Cc: oracle-l_at_freelists.org
Subject: RE: Union all very slow.

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