Query not using the Bitmap index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Query not using the Bitmap index

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I have a table with 1498587 rows. The users query this data by year, and to improve this query I created a index on year, but the query didn't use this index because of the uniqueness of this index. Therefore I decided to create a bitmap index, but the query still doesn't use the bitmap index. Why?
    This show how many records for each year.
    ACT_YEAR COUNT(*)
    ---------- ----------
    93 21858
    94 168088
    95 169920
    96 163749
    97 168262
    98 180296
    99 185229
    2000 202371
    2001 197180
    2002 13665
    27969


  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    a) Make sure you've analyzed the tables. You should do both an

    "analyze table xxx compute statistics"

    and

    "analyze table xxx compute statistics for all indexed columns"

    as the latter will give you the histogram data which Oracle will use when determining if a specific value is selective enough.

    b) Check your query to see if it is using hard-coded values for the year or bind variables. If it is using bind variables, these will appear as :b1, :b2 etc rather than '99', '2001'. You can find out the SQL being executed by using SQL*Trace or by viewing the SQL through something like DBA Studio while it's executing.

    If bind variables are being used, then Oracle will not be able to look at the actual data values when it decides how to do the query. It will have to make the decision based on the general selectivity of the column and this is quite low for the "year" column, so it's unlikely to use an index.

    If the query is using bind variables, you could try switching it to hard-coded values. However, this may be difficult if the query is being issued through a front-end over which you have no control.

    c) Check that the datatype of the column matches the data-type being passed in. If your year column is numeric but the query contains '99', you are comparing a string to a number and Oracle will have to do a data conversion. This wll suppress any index use.

    d) Even after all this, with the distribution of the data that you have, I think Oracle may not choose to use the index. This is because there aren't that many distinct values.

    A bitmap index is even less likely to be used in this circumstance than a btree because bitamp indexes are intended to be used in combination with other fields. If there are some other fields you can bring into the query which always have a particular set of values (and also have bitmap indexes), then this may help.

    As a final resort, you can force Oracle to use the index with a hint:

    select /*+ INDEX(...) */
    ...



    [Edited by Scorby on 02-05-2002 at 09:38 AM]

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    I have analyze the table.
    This is the query used:
    select * from schedule
    where act_year=2001;

    The act_year column is datatype number(22)!!

    This is the explain plan:
    Query Plan
    ----------------------------------------------------------------------------------------------------
    SELECT STATEMENT Cost = 3628
    TABLE ACCESS FULL FMC_SCHEDULE 1


    I thought bitmap indexes should be used on low cardinality columns.

  4. #4
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    You're right up to a point. Bitmap indexes are designed for combinations of low cardinality columns e.g. you have a query with:

    year = 2000 = 500000 rows
    product = 'X' = 400000 rows
    account = 'Y' =100000 rows

    But:
    2000 + 'X' + 'Y' = 500 rows

    But they're not designed to work with a single columns.

  5. #5
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    Another option open to you would be to partition Schedule on year. A query like:

    select * from schedule
    where act_year=2001

    would then show up as doing a full table scan but it would only actual scan one partition of the table.

  6. #6
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    To partition the table was my next option, but the Patition option has not been installed.

    Therefore, for a quick fix I wanted to use an index. I can't use hints in the query as the users use 'Crystal reports' to run queries.

    I will use optimizer_mode=rule in the init.ora file, and create a 'normal' index on this column. This has worked.
    I have found that the CBO is not as efficient as RBO!!

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    The parameter b_tree_bitmap_plans must be set to TRUE in the init.ora file in order to use bit map indexes.
    Also create_bitmap_area_size and bitmap_merge_area_size in the init.ora file should have a large value.

  8. #8
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    I have never heard of this parameter and can't find it in my 8.1.6 database. Is it new ?

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Parameter B_TREE_BITMAP_PLANS is obsolete in 8i and above.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Feb 2001
    Posts
    119
    if your optimizer is rule bit mapindexes will not be used ??
    check that

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