Can oracle merge bitmap indexes during fast full scan?

Question

I have a large fact table with 300M rows and 50 columns in it. There are multiple reports over this table and each report uses only couple out of 50 columns from the table.

Each column in the fact table is indexed with BITMAP INDEX. The idea is to use these indexes as a one-column version of the original table assuming that oracle could merge BITMAP INDEXes easily.

If I use several columns from the table in WHERE statement, I can see that oracle is able to merge these indexes effectively. There is BITMAP AND operation in execution plan as expected.

If I use several columns from the table in SELECT statement, I can see that depending on columns selectivity, oracle is either performing unneeded TABLE ACCESS or BITMAP CONVERSION [to rowids] and then HASH JOIN of these conversions.

Is there any way to eliminate the HASH JOIN in case of joining several BITMAP INDEXes? Is there any hint in oracle to force BITMAP MERGE when columns appear in SELECT statement rather than WHERE?

Intuitively it seems like the HASH JOIN for BITMAP INDEXes is unneeded operation in SELECT statement taking into account it is indeed unneeded in WHERE statement. But I couldn't find any evidence that oracle could avoid it.

Here are some examples:

SELECT a, b, c /* 3 BITMAP CONVERSIONs [to rowids] and then 2 unneeded HASH JOINS */
  FROM fact;

SELECT a, b, c, d, e /* TABLE ACCESS [full] instead of reading all the data from indexes */
  FROM fact;

SELECT a /* BITMAP INDEX [fast full scan] as expected*/
  FROM fact
  WHERE b = 1 and c = 2; /* BITMAP AND over two BITMAP INDEX [single value] as expected */

Are there any hints to optimize examples #1 and #2?

In production I use oracle11g but I tried similar queries on oracle12c and it look like in both versions of oracle behave the same.

Answer 1

After some research it looks like oracle12c is incapable of joining BITMAP INDEXes if they are used in SELECT clause efficiently.

There is no dedicated access path to join BITMAP INDEXes in SELECT clause and so HASH JOIN is used in this case.

Oracle cannot use BITMAP MERGE access path in this case as it performs OR operation between two bitmaps:

How Bitmap Merge Works A merge uses an OR operation between two bitmaps. The resulting bitmap selects all rows from the first bitmap, plus all rows from every subsequent bitmap.

Detailed analysis showed that only HASH JOIN was considered by cost optimizer in my case. I wasn't able to find any evidence that BITMAP INDEXes could be used efficiently in SELECT statement. Oracle documentation suggests using BITMAP INDEXes only in WHERE clause or joining fact to dimensions.

And either of the following are true:

• The indexed column will be restricted in queries (referenced in the WHERE clause).

or

• The indexed column is a foreign key for a dimension table. In this case, such an index will make star transformation more likely.

In my case it is neither of the two.

Answer 2

I think what you are seeing is essentially the "index join access path" in action :) Oracle needs to join the data from both scans on ROWID, to stitch the rows together. The hash join is the only method open to Oracle. The fact that you are using bitmap indexes is actually irrelevant; you see the same behaviour with b-tree indexes

-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                  |  1973K|    43M|   137K (30)| 00:00:06 |
|   1 |  VIEW                  | index$_join$_001 |  1973K|    43M|   137K (30)| 00:00:06 |
|*  2 |   HASH JOIN            |                  |       |       |            |          |
|*  3 |    INDEX FAST FULL SCAN| IO               |  1973K|    43M| 17201  (78)| 00:00:01 |
|*  4 |    INDEX FAST FULL SCAN| IT               |  1973K|    43M| 17201  (78)| 00:00:01 |
-------------------------------------------------------------------------------------------