6 Replies Latest reply: Aug 3, 2009 5:56 PM by 601585 RSS

    Performance - BITMAP CONVERSION

    598622
      Hi experts;

      I have a table T (i varchar(5), j varchar(5));
      This table has two indexes basis function

      Create index id1 on T(lower(i));
      Create index id2 on T(lower(j));

      Select * from T
      where
      lower(i)='a'
      or
      lower(j)='a';

      this query has not the same explain plan on a Enterprise version or a Standard Edition.
      On the enterprise, Oracle make an "bitmap transformation" and use the both indexes.

      With a standard Edition, Oracle make a full scan of the table.
      I know bitmap index are only available on Enterprise, but I don't kwow if this the same for "Bitmap transformation".

      Thanks for you help !

      Laurent;
        • 1. Re: Performance - BITMAP CONVERSION
          P.Forstmann
          Testing published on J. Lewis blog http://jonathanlewis.wordpress.com/2009/05/08/ioug-day-4/ say bitmap conversion is an Entreprise only feature.

          Please post your 4 digits Oracle version.

          Edited by: P. Forstmann on Jul 30, 2009 4:31 PM

          Edited by: P. Forstmann on Jul 30, 2009 4:32 PM
          • 2. Re: Performance - BITMAP CONVERSION
            598622
            Hi,

            Thanks a lot.
            My version is 10.2.0.4 (64 bit). It's a big problem because, I host the application and can't modify the query !

            Laurent.
            • 3. Re: Performance - BITMAP CONVERSION
              598622
              Hi,

              But what i don't understand is :

              create index id3 on t(i);
              create index id4 on t(j);
              If I use b-tree index instead basis function, Oracle use the index (using a concatanation and not a bitmap transformation) !

              Why Oracle make a full scan with index basis function and not with b-tree index ?

              Thanks for your help.
              • 4. Re: Performance - BITMAP CONVERSION
                601585
                I'm sorry but you're stuck.

                http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_in.htm#1006464

                Oracle can't use function-based indexes with or-expansion.


                ==================================
                Dion Cho - Oracle Performance Storyteller

                http://dioncho.wordpress.com (english)
                http://ukja.tistory.com (korean)
                http://dioncho.blogspot.com (japanese)
                ==================================
                • 5. Re: Performance - BITMAP CONVERSION
                  486393
                  Why stuck, move the or to pl/sql?

                  create a deterministic pl/sql function and use this in your function based index defintion

                  Untested:

                  create or replace function test_or(p_i in varchar2, p_j in varchar2)
                  retun varchar2 deterministic
                  is
                  begin
                  if lower(p_i)='a' or lower(p_j) ='a' then
                  return 'Y';
                  else
                  return 'N';
                  end if;
                  end;
                  /

                  create index t_i in t (test_or(i,j));

                  use query: select * from t where test_or(i,j) = 'Y';
                  • 6. Re: Performance - BITMAP CONVERSION
                    601585
                    Why stuck, move the or to pl/sql?
                    Because OP has no direct control on the source code.

                    With direct control, we would have tons of ways to solve this problem. :)


                    ==================================
                    Dion Cho - Oracle Performance Storyteller

                    http://dioncho.wordpress.com (english)
                    http://ukja.tistory.com (korean)
                    http://dioncho.blogspot.com (japanese)
                    ==================================