Re: Partitioning or BitMap-Index?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Fri, 14 Feb 2003 04:39:24 +1100
Message-ID: <pan.2003.02.13.17.39.23.653643@yahoo.com.au>

On Thu, 13 Feb 2003 07:27:19 +0100, Christian Hartmann wrote:

> On Thu, 13 Feb 2003 03:38:16 +1100, "Howard J. Rogers"
> <howardjr2000_at_yahoo.com.au> wrote:
>

>>Is the column subject to change, ie DML? If so, run away from bitmap
>>indexes. A single bitmap index on a table is also not the world's most
>>useful thing -their power really comes from the combination of lots of
>>them.
>>
>>If you partition by this column of 1s and 0s, you are (presumably) going
>>to end up with two large slabs of data. Not sure if that would buy you
>>very much in terms of performance... it depends so much on what other
>>things are going on in your SQL. On the other hand, it wouldn't be
>>difficult to test.

>
> Yes, your are right it won't be dificult to test. On the other hand I like
> to get some opions from other users here.
>
> As I wrote in the other reply to the former posting I am currently using
> this column with possible values of 0 and 1. The data in this column is
> onyl created when the dataset is created in the table. There are no
> DML-Statements on this column and the column is used in every
> SQL-Statement witch run against the table in the where-clause.
>
> What do you think about it?

If there are 50% 0s and 50% 1s, then an index of any sort is going to be a waste of time. And as I said, a single bitmap index on a table is of very dubious value, unless your data is very highly skewed, and you are frequently looking for the rarer values.

Partitioning, as I said, I can't comment on. If your partition by range on 1 and 0, then it is *possible* that your SQL would have to look through 50% less records. But I bet it won't: presumably, other predicates are supplied, and the chances of those predicates all falling within the same partition as your '1' or '0' are negligble. Which means that Oracle will have to trawl through the other partition anyway. But it very much depends on what those other predicates are, how they're distributed, how they're indexed and so on.

But my gut feel is that neither approach is going to be of much use to you.

Regards
HJR Received on Thu Feb 13 2003 - 11:39:24 CST