One is All You Need

While being an incredibly powerful and efficient way of indexing certain types of data, it is now relatively well accepted that Oracle’s Bitmap Indexes still have some kinks and sometimes cause erratic behavior in queries, particularly when dealing with parallel queries.

Unfortunately, this erratic behavior can even crop up when parallelism has been set to zero both on a given table, and in a query itself, if you are trying to execute even simple queries that contain PARTITION BY clauses. Unfortunately, there often appears to be no rhyme or reason as to when queries will exhibit this behavior when querying one bitmap-indexed table versus another.

Nonetheless, you may have found yourself faced with the following situation, and simply need a workaround.

Consider:

A fact table, ORDER_ITEM_F of the following general structure:

This table has numerous records already in it:

> SELECT count(*) FROM order_item_f;

  COUNT(*)
----------
    657802

Additionally, among others, a bitmap index has been created on the location ID field (loc_id).

> CREATE BITMAP INDEX order_item_f_b_idx_11 ON order_item_f (loc_id)


Next, for some internal reporting you would like to query this table to find counts of order items per sales location, while attaching a total for the set to each row (perhaps you are using this query in an Oracle Report and doing some nice formatting).

A simple query could be as follows:

> SELECT  distinct loc_id,
          count(*) over (partition by loc_id) loc_total,
          count(*) over () tbl_total
  FROM    order_item_f
  ORDER
  BY      loc_id;

What you expect to receive from such a query is a row for each LOC_ID value, which includes a count of records having that LOC_ID, and an extra column containing the total number of records in the table (this value will be the same for each row returned).

Unfortunately, what you receive instead falls into one of the two scenarios below:

Scenario 1: Inaccurate counts based on full table count query (example above):

    LOC_ID  LOC_TOTAL  TBL_TOTAL
---------- ---------- ----------
        17         12         89
        18         12         89
        19          8         89
        20          9         89
        21          1         89
        22          3         89
        23          4         89
        24          6         89
        25          6         89
        26          1         89
        27         11         89
        28          6         89
        29          4         89
        30          3         89
        31          1         89
        44          1         89
        45          1         89

Scenario 2: ORA-0600 - The Dread Internal Error

ERROR at line 1:
ORA-00600: internal error code, arguments: [kxfqupp_bad_cvl], [7215], [4],
 [0], [], [], [], []

Enabling autotrace (>SET AUTOTRACE ON) and re-running the query reveals that the database attempted to retrieve your results using the bitmap index:

Execution Plan
----------------------------------------------------------
Plan hash value: 2270429775

-------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |    16
|   1 |  SORT UNIQUE                   |                       |    16
|   2 |   WINDOW SORT                  |                       |    16
|   3 |    WINDOW BUFFER               |                       |    16
|   4 |     BITMAP INDEX FAST FULL SCAN| ORDER_ITEM_F_B_IDX_11 |   657K
-------------------------------------------------------------------------

An inspection of the plan shows that ~657K records are in fact accounted for by the fast full scan of the bitmap index. Yet in the case of Scenario 1 above, the counts coming back clearly do not reflect this total (if they come back at all!).

The PARALLEL/BITMAP bug strikes!

While a much more in-depth discussion of the cause for this bug could follow, sometimes you just need a practical fix:

The Fix: Full Table Scan Hint

To quickly resolve your problem, a simple hint can be added to your SELECT query, to force a full table scan instead in place of the bitmap index scan:

> SELECT  /*+ FULL(order_item_f) */
          distinct loc_id,
          count(*) over (PARTITION by loc_id) loc_total,
          count(*) over () tbl_total
  FROM    order_item_f
  ORDER
  BY      loc_id;

    LOC_ID  LOC_TOTAL  TBL_TOTAL
---------- ---------- ----------
        17     127674     657802
        18     136815     657802
        19      49540     657802
        20      51782     657802
        21       3908     657802
        22      11463     657802
        23      33415     657802
        24      29190     657802
        25      34141     657802
        26       1901     657802
        27      97483     657802
        28      31405     657802
        29      32619     657802
        30      15248     657802
        31          1     657802
        44       1090     657802
        45        127     657802

17 rows selected.

	John Weicher