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

Answer 1

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.