Re: tom kyte reference to build an index

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 14 Dec 2005 01:41:12 +0100
Message-ID: <dnnpn2$oga$01$1@news.t-online.com>

gcherer_at_texas.net schrieb:
> In Tom's expert oracle database architecture book, i got stumped on
> page 7. He uses this sentence: "Create an index only on the
> processed_flag column when the processed_flag is N...". i am stumped
> on how to make an index on a column but only for certain values. In
> this case, the 'processed_flag' only has two values - N or Y. He is
> talking about creating an index only on the N values, which are
> relatively few and are the only rows of interest.
> So, the inexperienced idiot (me) asks, how the heck do you build an
> index only on a column with certain values??
> Please be gentle in your reply.
> TIA
>

He uses that fact , that NULL values are not indexed in a B tree index and build an function based index on the expression which returns 'N' for 'N' and NULL for 'Y' (somewhat like: case when processed_flag = 'N' then 'N' else NULL end).

Best regards

Maxim Received on Tue Dec 13 2005 - 18:41:12 CST