Xref: alice comp.databases.oracle.server:65180 Path: alice!news-feed.fnsi.net!newsfeed.eurocyber.net!newsfeed.tli.de!newsfeed.icl.net!colt.net!diablo.theplanet.net!news.theplanet.net!newspost.theplanet.net!not-for-mail From: Connor McDonald Newsgroups: comp.databases.oracle.server Subject: Re: Optimization of UNION Date: Thu, 09 Sep 1999 20:01:31 +0800 Organization: Customer of Planet Online Lines: 61 Message-ID: <37D7A19B.496D@yahoo.com> References: <7r85ot$agk@TGZ3> Reply-To: connor_mcdonald@yahoo.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news5.svr.pol.co.uk 936903399 16457 62.136.153.239 (9 Sep 1999 18:56:39 GMT) NNTP-Posting-Date: 9 Sep 1999 18:56:39 GMT X-Complaints-To: abuse@theplanet.net X-Mailer: Mozilla 3.01 (Win95; I) To: Michael Keppler Michael Keppler wrote: > > Hello everybody ! > > If I have to queries on the same columns of the same tables, will the > optimizer make a OR statement from a UNION? (I think, OR will be faster > normally, right?) > > For example, If I use > > select col1 > from table1 > where col2=xyz > union > select col1 > from table2 > where col3=abc > > will this be optimized to "where col2=xyz or col3=abc" ? > And if yes, can the optimizer do this also on queries which do not have > all (but some) the same tables in the select statement ? Could I give > the optimizer a hint for this ? > > Ciao, Michael. > > -- > Michael Keppler, MCSE > IT logic GbR > Michael.Keppler@gmx.de You can do an EXPLAIN which will tell you...Oracle will occasionally switch between the two... for example, you may issue "select ... union select ..." and oracle just does the one scan of the table (ie as if it were an OR), and vice versa... 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 HTH -- =========================================== Connor McDonald "These views mine, no-one elses etc etc" connor_mcdonald@yahoo.com "Some days you're the pigeon, and some days you're the statue."