Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> UNion Versus Or
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 <insert sample criteria similar to that above> 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?" - DittoReceived on Tue Sep 09 2003 - 13:33:31 CDT