Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news-out.visi.com!petbe.visi.com!news.state.mn.us!not-for-mail From: Turkbear Newsgroups: comp.databases.oracle.server Subject: Re: UNion Versus Or Date: Tue, 09 Sep 2003 14:49:41 -0500 Organization: Minnesota Governmental/Educational Agencies Lines: 90 Message-ID: References: <3F5E1CFB.5C3DBA9@Gunner.Sabot.Spam.On.Loaded.FIRE> Reply-To: johng@mm.nospam.com NNTP-Posting-Host: gateway.dot.state.mn.us Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: news.state.mn.us 1063136981 29833 156.98.4.11 (9 Sep 2003 19:49:41 GMT) X-Complaints-To: news@news.state.mn.us NNTP-Posting-Date: Tue, 9 Sep 2003 19:49:41 +0000 (UTC) X-Newsreader: Forte Agent 1.93/32.576 English (American) Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242721 Tim Marshall wrote: >JOhn Palinski's book recommends using Union set operators instead of OR >conjunctions in the where clause with respect to obtaining better >perfromance. I was reminded of this when I was browsing through some of >the examples JOhn Russell provided me in a search URL in another thread >(http://otn.oracle.com/pls/db92/db92.drilldown?remark=&word=inline+views&book=&preference=). > >How would I "unionize" something like this: > >A user has a set of about 35 different criteria from which he can choose >to limit a recordset return (I'm probbably using incorrect terminology >here). IN about 28 of those, he can choose multiple criteria. > >To deal with such multiple criteria, I used OR in my where clauses in >the Jet version of the aplication I'm working on now (the Access/Jet app >merely periodically copied records from Oracle tables into several, very >denormalized, Jet tables). But how would one deal with, for example >(I'lll use names of criteria, rather database values and PK/FKs which is >what I'd program into my forms to create the SQL I need): > >Entity: FACILITY (FAC) > >Paton College (PC) >Burton's Pond (BP) > >(these are both two separate groups of student residence buildings) > >Entity: JOB TYPE (JT) > >Grounds Work (GW) >Carpentry Work (CW) >HVAC Work (HW) >Locksmith Work (LW) > >Entity: WORK TYPE (WT) > >Scheduled Maintenance (SM) >Response to Client Requests (CR) > >etc, etc > >The above might be a typical sort of inquiry and in my Jet application, >after the select and from clauses, the where clause would look something >like: > >(FAC = PC OR FAC = BP) AND (JT = GW OR JT = CW OR JT = HW OR JT = LW) >AND (WT = SM OR WT = CR) > >I was initially planning to do something similar in my Oracle app, but >am wondering now about UNIONs. > >To achieve the above with UNions, I would have to have 2 x 4 x 2 >separate select statements all joined by a UNION operator (I won't >bother to try to work this out). > >Am I right in thinking the hassle to write code to create such union >statements (and the above sample criteria could be greatly varied) is >not worth the performance benefits, or should I really try to figure a >way to deal with this with UNions. Those of you who read my other >recent post will realise such UNions would also have each up to 9 inline >view in them as well. > >Maybe my whole approach to this is silly, but I know managers just want >to be able to say "I wanna look at the costs and person hours associated >with but I'm going to get >bored fast if I have to calculate it for each and every selection I make >for every entity (most managers, as you know haven't got a clue what >this word means) I'm interested in." I should know, as I'm one of those >managers... > >Anyway, any advice on what to do, OR versus Union would be appreciated. >I'm wondering if using UDT/ADTs as Billy suggested in another thread >might be another possibility.... > >Thanks in advance. You could also try using the IN operator: where ( FAC IN (' PC',' BP') AND JT IN ('GW',' CW','HW',' LW') AND WT IN ('SM','CR') ) With this construct, you can build the IN strings dynamically ( if you are using something like ASP or JavaScript to get the user's input) It may not be more efficient but may be easier to read and modify..