Forum Stats

  • 3,770,165 Users
  • 2,253,079 Discussions
  • 7,875,353 Comments

Discussions

Bitmap index on number column x char column

Handerson
Handerson Member Posts: 22
edited Jul 23, 2013 6:55PM in SQL & PL/SQL

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

Tagged:

Answers

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Jul 23, 2013 12:32PM

    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
    Handerson Member Posts: 22

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

    Thanks and B regards

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Jul 23, 2013 1:21PM
    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
    Greg Spall Member Posts: 972
    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
    Etbin Member Posts: 8,968 Gold Crown

    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
    Greg Spall Member Posts: 972
    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?

  • 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.

This discussion has been closed.