Re: Optimization of UNION

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 10 Sep 1999 18:14:58 +0800
Message-ID: <37D8DA22.51F@yahoo.com>

Jonathan Lewis wrote:
>
> Was it 7.3.4 perhaps - there a a few things
> that are in 7.3.4 and 8.1 but not in 8.0.
>
> This optimizer trick is a new one on me though.
> I knew that an IN list could be converted to a UNION ALL,
> but I didn't think it could work the other way round, and
> when I read the post I thought it VERY unlikely that a
> UNION would be converted.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Connor McDonald wrote in message <37D7A19B.496D_at_yahoo.com>...
> >
> >Interesting, I was giving a tuning course the other day, and this
> >intersting thing came up
> >
> >In 7.3
> >
> >select ...
> >from table
> >where un_indexed_col = val1
> >UNION ALL
> >select ...
> >from table
> >where un_indexed_col = val1
> >
> >was (correctly) changed to OR and thus one scan by the optimiser...The
> >same thing in 8.0.5 (same schema, blocksize, tables etc) did not...Hmmm
> >

7.3.3.4 actually...

I was sitting there praising the wonder of the Oracle optimizer until the class ran the thing on 8.0 :-)

I agree though - I would be extremely impressed if the optimizer could gets it teeth successfully around transferring union (as opposed to union all)

--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Fri Sep 10 1999 - 05:14:58 CDT