7 Replies Latest reply: Jul 23, 2013 5:55 PM by rp0428 RSS

    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

        • 1. Re: Bitmap index on number column x char column
          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

          • 2. Re: Bitmap index on number column x char column
            Handerson

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

             

            Thanks and B regards

            • 3. Re: Bitmap index on number column x char column
              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

              • 4. Re: Bitmap index on number column x char column
                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

                • 5. Re: Bitmap index on number column x char column
                  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

                  • 6. Re: Bitmap index on number column x char column
                    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?

                    • 7. Re: Bitmap index on number column x char column
                      rp0428

                      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.