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