Oracle Function based Bitmap Index

Question

I'm using Oracle 11g and I have three tables, viz, TABLE_1, TABLE_2, TABLE_3. In a select statement I need to execute the following query:

SELECT 
    -- // ommitted
FROM
    TABLE_1,
    TABLE_2,
    TABLE_3
WHERE
    -- // ommitted
    AND NVL(TABLE_1.COL_1, 0) = NVL(TABLE_2.COL, 0)
    AND (TABLE_1.COL_2 = TABLE_3.COL OR NVL(TABLE_1.COL_2, 0) = 0)

I want to create function based bitmap index for the followings:

NVL(TABLE_1.COL_1, 0) = NVL(TABLE_2.COL, 0)
(TABLE_1.COL_2 = TABLE_3.COL OR NVL(TABLE_1.COL_2, 0) = 0)

Is it possible?

For NVL(TABLE_1.COL_1, 0) = NVL(TABLE_2.COL, 0) I have tried:

CREATE BITMAP INDEX TABLE_1_TABLE_2_NVL_COL_IDX 
ON     TABLE_1 (TABLE_2.COL) 
FROM   TABLE_1, TABLE_2
WHERE  NVL(TABLE_1.COL_1, 0) = NVL(TABLE_2.COL, 0);

But it has thrown the error:

ORA-25954: missing primary key or unique constraint on dimension
25954. 00000 -  "missing primary key or unique constraint on dimension\n"
*Cause:    An attempt to create a join index was made, which failed
           because one or more dimensions did not have an appropriate
           constraint matching the join conditions.
*Action:   Ensure that the where clause is correct (contains all of the
           constraint columns) and that an enforced constraint is on
           each dimension table.

If I'm able to create the indexes, then is the following syntax would be the right way to provide hints in the select statement?:

SELECT 
    /*+ INDEX (TABLE_1 TABLE_1_TABLE_2_NVL_COL_IDX) */
    /*+ INDEX (TABLE_1 TABLE_1_TABLE_3_NVL_COL_IDX) */
    -- // ommitted

The join predicate `NVL(TABLE_1.COL_1, 0) = NVL(TABLE_2.COL, 0)` creates the cartesian product of all rows in *TABLE_1* having *null* in *COL_1* with all rows in *TABLE_2* having *null* in *COL*. If there are 1000 rows with a *null* value in each table, the cartesian product will have 1,000,000 rows. Is this really what you want? — Codo, Jan 10 '19 at 14:27
@Codo, when this predicate is used in the where clause there are filterings on some other column of `TABLE_1` and `TABLE_2`, so I would say the cartesian product wouldn't be that much. But if I am able to create such an index then indeed the index would be big. Do you think such an index is unrealistic and will do more harm than good? — Tapas Bose, Jan 10 '19 at 14:41

score 3 · Accepted Answer · edited Jan 10 '19 at 16:28

Bitmap join indexes are subject to a number of restrictions. Namely:

You cannot create a function-based join index.

The dimension table columns must be either primary key columns or have unique constraints.

The first rules out having nvl ( col, 0 ) in the index

The second explains the error you're getting. You need to add a primary or unique constraint on table_2.col. Which also implies that there should be no null values in this column!

So you're going to need a different approach to indexing for this query.

Oracle Function based Bitmap Index

1 Answers1