What's the difference between BITMAP MERGE and BITMAP OR in Oracle?

Question

When I check the documentation, I saw that, the BITMAP MERGE performs an OR operation between the bitmaps. So, why there is also a BITMAP OR then? Or, what are the differences between them?

Bests

Answer 1

They are very similar, it's true.

BITMAP MERGE takes output from a single plan operation that returned multiple bitmaps (e.g, the output of a RANGE SCAN operation) and merges those multiple bitmaps into one.

For example, suppose I submit:

SELECT 'x'
FROM   t
WHERE  col1 BETWEEN 'A' and 'C'
AND    col2 = 'X';

Remember that in a bitmap index, there is a bitmap associated with each key. These are the multiple bitmaps that must be merged. The plan will look like this:

SELECT STATEMENT
  TABLE ACCESS T BY INDEX ROWID
    BITMAP CONVERSION TO ROWID
      BITMAP AND
        BITMAP MERGE
          BITMAP INDEX COL1_IDX RANGE SCAN
        BITMAP INDEX COL2_IDX SINGLE VALUE

... more or less

In this case, Oracle is scanning the bitmap index on COL1 to get the distinct index key values between 'A' and 'C'. Then, it reads the index for the bitmaps associated with each value and merges them together via OR.

NOTE: the reason for the COL2 = 'X' in the above example is because I think Oracle only uses the BITMAP MERGE if it needs to generate a single bitmap to pass into a parent step that requires one (in this example, that step is the BITMAP AND step).

Contrast that with a BITMAP OR plan step. BITMAP OR takes output from two plan operations that each output a single bitmap. It that merges those into a single bitmap.

Suppose I submit:

SELECT 'x'
FROM   t
WHERE  col1 = 'A'
OR     col2 = 123;

Oracle must start with an access path -- a way to use the index to get the data it needs.
In this case the access path will be a BITMAP INDEX ... SINGLE VALUE operation on the indexes associated with COL1 and COL2. It will then use BITMAP OR to merge the results of those two access paths. The plan will look something like this:

SELECT STATEMENT
  TABLE ACCESS T BY INDEX ROWID
    BITMAP CONVERSION TO ROWID
      BITMAP OR
        BITMAP INDEX COL1_IDX SINGLE VALUE
        BITMAP INDEX COL2_IDX SINGLE VALUE

... more or less (I didn't actually test this -- I am just conveying my understanding)