Re: Partitioning or BitMap-Index?

Hello!

One consideration with partitioning is how/which indexes to use. Global indexes on partitioned tables are generally larger, because extended rowids (10 bytes) have to be used instead of restricted rowids (6 bytes). When you have many partitions (e.g. not only two) and you use only global indexes - you might miss a lot of good features of partitioning. However, if you always use local and global partitions on same columns together, dml speed and storage might become an issue.

But if your query *always* has the check whether the column is 0 or 1 you don't need global indexes on this table, only local ones, because er.. was it called partition pruning will do the job for you. Anyway, even if you had the global index starting with that column, it probably wouldn't be used anyway, because it's too unselective..

Tanel.

"John Russell" <netnews5_at_johnrussell.mailshell.com> wrote in message news:jq9m4vgo2qsg0as5g3ea91l5qc29eu0154_at_4ax.com...
> On 12 Feb 2003 04:17:53 -0800, cha_at_christian-hartmann.de (Christian
> Hartmann) wrote:
> >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.
>
> I recall reading (don't have the URL handy) that if the data is evenly
> distributed like this across just a couple of values, like 0/1 or M/F,
> the bitmap index is not recommended. If you do a query on all the '0'
> values or all the '1' values, chances are it's going to have to read
> all the data blocks anyway, so going through the index hasn't really
> saved you any I/O.
>
> Don't have any experience myself with partitioning. (Not yet!)
>
> John
Received on Thu Feb 13 2003 - 06:07:30 CST