Bitmap index on number column x char column

Handerson

Hi everyone.

is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.

My application has a querie like this:

select ass.column20,

         ass.column30

				from table_a puc
					inner join table_b ass
						on ass.column1 = puc.column1
					where pc.column_char = 'S'
					and ass.column_char02 = 'P'

If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.

This table has 4.000.000 rows.

Is there a explanation for this behavior?

Oracle 11.2.0.2

SO: AIX

Thanks in advance.

Handerson

Etbin

It might be simply because there are at least 256 different characters but only 10 different digits.

The former might not be considered low cardinality valued, especially when statistics might be missing.

Regards

Etbin

Handerson

But the char column has a check constraint that allows only two values.... Do this no matter?

Thanks and B regards

Etbin

Handerson wrote:
But the char column has a check constraint that allows only two values.... Do this no matter?

I'm afraid it does. Never saw a plan using an index to retrieve say more than 20% of rows.

My personal (optimistic) guess would be somewhere around 15%

Have you collected statistics - were histograms formed ?

If not even distribution is assumed so it's 10% for the number column and 50% for the char column - due to the constraint.

Regards

Etbin

Greg Spall

Etbin wrote:

My personal (optimistic) guess would be somewhere around 15%

I've always seen this mentioned, and I've never really figured out where it came from. However, after reading these forums and Tom Kyte for some years, I've learned to doubt this number

Quick search found this article - doesn't seem to be too far off - and (to me anyway) makes sense as to where this number comes from:

http://www.confio.com/logicalread/oracle-11g-when-to-use-index-mc02/#.Ue6-IdLVDLY

In short, if you on 10i or 11g, that 15% is much lower, like 2-4% now Those full scans are getting better! lol

Etbin

Once you write never, you have to use a sound upper bound if you don't want to have some counterexample provided.

Most probably it might be forced by a hint, but the claim the index was used would stand)

Murphy's planning method might be most probably safe:

to_char(2 * :probable_time) || next_bigger_time_unit(:time_unit)  Example; 3,'days' => '6 weeks'

Version 12 adaptive query might make things still different for sure, but for the better or for the worse ? It depends ...

Regards

Etbin

Greg Spall

Etbin wrote:

Once you write never, you have to use a sound upper bound if you don't want to have some counterexample provided.
Most probably it might be forced by a hint, but the claim the index was used would stand)
Murphy's planning method might be most probably safe:
 to_char(2 * :probable_time) || next_bigger_time_unit(:time_unit)  Example; 3,'days' => '6 weeks' 
Version 12 adaptive query might make things still different for sure, but for the better or for the worse ? It depends ...


Regards


Etbin

s'truth!

But isn't that more like Montgomery Scott's rule for estimating, not Murphy?

Unknown

is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.
. . .

If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.
This table has 4.000.000 rows.
Is there a explanation for this behavior?

Post the proof that supports your contention that the 'plan is not changed'. That proof needs to include:

1. ALL of the statements you executed that show that stats were collected and current before each test

2. The table and index DDL

3. row counts for the tables involved

4. row counts for the predicates used

No - there is no performance difference due simply to the datatype of the underlying column. Any performance difference would likely be due to differing numbers of values for the column or the stats being missing or stale.

Your example begs the question: why are you even using a bitmap index to begin with? Bitmap indexes are generally most useful when used with other bitmap indexes on the same table. And they would seldom be useful, or used, if there were only two possible values for the data being indexed.