Path: text.usenetserver.com!out01a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!r34g2000hsd.googlegroups.com!not-for-mail
From:  William Robertson <williamr2019@googlemail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: indexing a column with only 2 or 3 values
Date: Sat, 04 Aug 2007 04:35:26 -0700
Organization: http://groups.google.com
Lines: 32
Message-ID: <1186227326.171460.134940@r34g2000hsd.googlegroups.com>
References: <1186127580.686735.115470@i38g2000prf.googlegroups.com>
   <5hg5aiF3kia4nU1@mid.individual.net>
   <1186127924.502727.241260@x40g2000prg.googlegroups.com>
   <5hg5rcF3jr7f6U1@mid.individual.net>
   <1186128333.403137.252020@x40g2000prg.googlegroups.com>
   <1186129309.937133.142260@g4g2000hsf.googlegroups.com>
   <1186130873.421687.317850@q75g2000hsh.googlegroups.com>
NNTP-Posting-Host: 82.45.190.94
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1186227326 30365 127.0.0.1 (4 Aug 2007 11:35:26 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 4 Aug 2007 11:35:26 +0000 (UTC)
In-Reply-To: <1186130873.421687.317850@q75g2000hsh.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X; en-US; rv:1.8.1.6) Gecko/20070725 Firefox/2.0.0.6,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: r34g2000hsd.googlegroups.com; posting-host=82.45.190.94;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.server:432415
X-Received-Date: Sat, 04 Aug 2007 07:35:26 EDT (text.usenetserver.com)

On Aug 3, 9:47 am, ciapecki <ciape...@gmail.com> wrote:
> On 3 Aug., 10:21, Helma <bad_elef...@hotmail.com> wrote:
>
> > > > >>> Does indexing a very big table (about 5Mio records) on the columnA
> > > > >>> which can hold only values Y,N,<NULL> make sense?
> > > > >> Yes, that's what bitmap indexes were made for.
>
> > FYI , you can also index only the N ( or Y) value's in the column.
> > This is done if e.g. the boolean is an indicator whether a row has
> > gone through a processing run or not, and the application just need to
> > find the N values.
> > Bitmap indexes are not ok if the table is updated intensively.
>
> > Helma
>
> Hi Helma,
>
> It  will be actually a view and will be refreshed everyday (once a
> day).
> But thanks for the warning.
>
> BTW. how can you index only N's for example?
>
> chris

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. This might also work for Y/N/NULL if the
null values formed the vast majority, although I would be interested
to know what nulls represent in a Yes/No column.