Re: UNion Versus Or

On Tue, 09 Sep 2003 16:03:31 -0230, Tim Marshall <tmarshal_at_Gunner.Sabot.Spam.On.Loaded.FIRE> wrote:

>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 believe (based on conversations here) if you use IN (e.g. FAC IN ('PC','BP')) that under the covers it will act like a UNION (or UNION ALL). I have found UNION valuable for performance when there are two distinct parts of the query, each of which depends on a different index. For example

select * from my_table where func(col) = 'value'; select * from my_table where other_func(other_col) = 'other_value';

Each of these can be made efficient by using a function-based index. (Note the hyphen. :-) But putting them in a single query with OR would mean that one of the indexes would be used, and the other function would be called unnecessarily for every row of the table (potentially very slow). Doing them as a UNION means each side of the query gets to use its own index.

For queries with lots of AND and OR operators comparing columns with literal values, something to look at is bitmap indexes. They take up more space and take longer to update when you make changes in the table. For each value that's in the column, there's a bit string that identifies which rows match that value. That way, they know immediately which set of rows matches FAC = 'PC', JT = 'GW', etc. The ANDs and ORs are evaluated very efficiently by ANDing and ORing long strings of bits.

John

--
Photo gallery: http://www.pbase.com/john_russell/