DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Bitmap index is not working

  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Bitmap index is not working

    Hi,
    I am new in this forum.
    I am getting one problem in using bitmap index in a table.

    I am having one table named tab1.

    tab1
    (a NUMBER,
    b INTEGER,
    c NUMBER,
    d DATE DEFAULT sysdate,
    e INTEGER DEFAULT 0,
    f VARCHAR2(100 BYTE),
    g NUMBER,
    h INTEGER DEFAULT 0)

    having B-Tree index on
    a,trunc(d) and c
    and having bitmapped index on e.

    E is heavily updated by application and having values only 0,8,1003.

    When I am using the below query:
    select * from tab1 where e=0 ; It is not using bitmapped index.

    Now I have created one table named tab2 having same column structure but having only bitmapped index on column e.

    Here I am using the below query:
    select * from tab2 where e=0 ; It is using index while search.

    can anybody help me out the reason?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    There are two different matters here.

    1- Oracle optimizer would rely on indexes only if indexes are cost-effective. If cardinality of e=0 is too high Oracle may decide FTS is more efficient.

    2- Bitmap indexes are not supposed to work alone. They are supposed to team up with other bitmap indexes in a process called star-transformation.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2009
    Posts
    6

    Reply from AWR and ADDM Reports

    Hi,
    I had analyzed AWR and ADDM Reports. There it was showing that the query running on bitmapped indexed column taking Highest Physical Reads and next to highest buffer gets. The CPU time is also higher for that query.

    I had tried using hints to forcefully use the index but it was not using the index.

    And also from ADDM Reports it is showing high IO for that query. Please find the details from ADDm reports:


    RATIONALE: The I/O usage statistics for the object are: 3209 full object
    scans, 9849769 physical reads, 144 physical writes and 0 direct
    reads.
    RATIONALE: The SQL statement with SQL_ID "1jvz9xkmfp0jq" spent
    significant time waiting for User I/O on the hot object.
    RELEVANT OBJECT: SQL statement with SQL_ID 1jvz9xkmfp0jq

    I had deleted the original query .
    The query was :
    select * from tab1 where e=0 and rownum<3000;

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    you didn't read my post, don't you?

    How is data distribution for your 0,8,1003 values on "e" column? is it any close to normal distribution?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2001
    Location
    India
    Posts
    57
    Hi,

    Yes. Pablo is right. It depends on data distribution only for index scanning.
    Thanks and Regards,
    Gitesh Trivedi
    Dbametrix Solutions
    Database DBA support

  6. #6
    Join Date
    Sep 2009
    Posts
    6

    Bitmap index

    Hi,
    In the table the data meaning is as follows:
    0 -> command has not been populated and just the data is inserted in the table.
    8 -> Command executed successfully .
    1003 -> command execution failure.

    Like these data are distributed.

    Once data is inserted in the table the status field is inserted =0. after commit when process starts the fields are updated as 5 where it is showing the commands are psocessing and after that the filelds are updated as 8 and 1003 according to the command execution status.

    But why I am asking the question that:
    How bitmap index depends on other index,

    because in tab1 the bitmap index is not working with other indexes for the query.

    But if I am creating same table (tab2) with same column but only bitmap index is there , then the bitmap index is working for the same query.

    One thing I have done today for the table tab1.

    I have dropped bitmapped index and created one B-Tree index, but still I have find out the indes is not working.

    Can you please share your knowledge that how index works with other indexes?

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by kallol.chakra View Post
    0 -> command has not been populated and just the data is inserted in the table.
    8 -> Command executed successfully .
    1003 -> command execution failure.

    Like these data are distributed.

    Once data is inserted in the table the status field is inserted =0. after commit when process starts the fields are updated as 5 where it is showing the commands are psocessing and after that the filelds are updated as 8 and 1003 according to the command execution status.
    That's the logic of the application not the way data is distributed.

    We are asking for cardinality, meaning how many rows have e=0, how many rows have e=8 and how many rows have e=1003

    Research indexes + cardinality
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Mar 2001
    Location
    India
    Posts
    57
    Hi,

    What is explain plan when you are not using
    1) select * tab1 where e=0;
    2) select a,b,c from tab1 where e=0;

    Did you generate histogram using dbms_stats for same table?

    Thanks and regards,
    Gitesh Trivedi
    http://www.dbametrix.com

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by giteshtrivedi View Post
    What is explain plan when you are not using
    1) select * tab1 where e=0;
    2) select a,b,c from tab1 where e=0;
    1- Little confused about "not using" phrasing.

    2- Provided stats do not change in between execution of the two queries execution plan would be dependent on predicate - I see the same predicate on both queries.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Mar 2001
    Location
    India
    Posts
    57
    Hi,

    Sorry that was typo error.
    Did you generate histogram for same column on table?
    What is distinct values and count in column "e"?
    Thanks and Regards,
    Gitesh Trivedi
    Dbametrix Solutions
    Database DBA support

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width