Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!snoopy.risq.qc.ca!News.Dal.Ca!coranto.ucs.mun.ca!not-for-mail From: Tim Marshall Newsgroups: comp.databases.oracle.server Subject: UNion Versus Or Date: Tue, 09 Sep 2003 16:03:31 -0230 Organization: Memorial University of Newfoundland Lines: 79 Message-ID: <3F5E1CFB.5C3DBA9@Gunner.Sabot.Spam.On.Loaded.FIRE> NNTP-Posting-Host: fm-zhukov2.facman.mun.ca Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: coranto.ucs.mun.ca 1063132411 12882 134.153.228.205 (9 Sep 2003 18:33:31 GMT) X-Complaints-To: usenet@coranto.ucs.mun.ca NNTP-Posting-Date: Tue, 9 Sep 2003 18:33:31 +0000 (UTC) X-Mailer: Mozilla 4.77 [en] (Win95; U) X-Accept-Language: en Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242717 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. -- Tim - http://www.ucs.mun.ca/~tmarshal/ ^o< /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake /^^ "Want some?" - Ditto