Re: UNion Versus Or

Tim Marshall <tmarshal_at_Gunner.Sabot.Spam.On.Loaded.FIRE> wrote in message news:<3F5E1CFB.5C3DBA9_at_Gunner.Sabot.Spam.On.Loaded.FIRE>...
> 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.
>

Don't worry. "Union all" sometimes improves performance, but in many cases optimiser would creat plan using "union all". In come cases full table scan is better, in this case union all is worse than OR. Usual tuning routine: make your statement to return correct results first, then tune it if performance is bad. Only in case when you can't tune it, try to rewrite it.

> 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.
Received on Thu Sep 11 2003 - 09:37:26 CDT