Re: indexing a column with only 2 or 3 values

On Aug 4, 4:51 pm, Helma <bad_elef..._at_hotmail.com> wrote:
> > Ideally you might arrange the design such that the small number of
> > rows you typically want to identify are flagged with a 'Y' (say) and
> > the rest left null. Then a standard btree index on that column would
> > be compact and efficient.
>
> Redesign? What's wrong with my suggestion to create an index on only
> the Y value's?
>
> H.

Nothing. It can be awkward to remember to use a particular NULLIF/ DECODE/CASE expression each time you need to use that column in a WHERE clause, although of course you might embed the expression in a view, code it in a procedure or define it in Business Objects or whatever and never have to care about it again, so it's not necessarily a big deal.

If the purpose of the column is to flag rows with a particular status or condition ('needs processing' etc) then storing the opposite value for all the other rows might be somewhat redundant (you might never use "where needs_processing = 'N'). Received on Sun Aug 05 2007 - 11:50:18 CDT