Re: Partitioning or BitMap-Index?

From: David Kurtz <info_at_go-faster.co.uk>
Date: Wed, 12 Feb 2003 13:00:16 -0000
Message-ID: <Hzr2a.720$VY5.76377@newsfep1-win.server.ntli.net>

It rather depends what you are doing with this table.

If you are updating this column then partitioning is probably not a great idea because you would move the row from one partition to the other. Lots of extra work involved.
Similarly, if you are regularly undating the table a bitmap index may not be a good idea either. Updating the index can be expensive and the index can grow very rapidly.

Are you using this column as a flag to find the rows that you want to process? In which case have you considered what is sometimes called a 'sparse index'.
If the column had values 1 and NULL (and most of the rows had value NULL in this column), then only the rows with value 1 would appear in the index, and the index would efficiently identify the rows.

If you have to use not null values in all rows then every row will be in the index. A histogram may also help if the data is mostly in one value, but only if the queries use a litteral value on this column.

---

David Kurtz
Go-Faster Consultancy Ltd.
web: www.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

"Christian Hartmann" <cha_at_christian-hartmann.de> wrote in message news:7c275508.0302120417.53d37f62_at_posting.google.com...
> Hi there,
>
> I have 8.1.7 database and a table with a column with values of 0 and
> 1.
>
> The column is used in every sql-statement against the table.
>
> For this low cardinality I wonder wheter a bitmap-index or a
> partitioning is the best way to improve the performance of this table.
>
> Any suggestion?
>
> Regards,
>
> Christian Hartmann
Received on Wed Feb 12 2003 - 07:00:16 CST