Bitmap Join Indexes-Indexes

Normally, an index is created on a single table, using only columns from that table. A bitmap join index breaks that rule and allows you to index a given table using columns from some other table. In effect, this allows you to denormalize data in an index structure instead of in the tables themselves.

Consider the simple EMP and DEPT tables. EMP has a foreign key to DEPT (the DEPTNO column). The DEPT table has the DNAME attribute (the name of the department). The end users will frequently ask questions such as “How many people work in sales?”, “Who works in sales?”, “Can you show me the top N performing people in sales?” Note that they do not ask, “How many people work in DEPTNO 30?” They don’t use those key values; rather, they use the human-readable department name. Therefore, they end up running queries such as the following:
$ sqlplus scott/tiger@PDB1
SQL> select count()from emp, deptwhere emp.deptno = dept.deptnoand dept.dname = ‘SALES’; SQL> select emp.from emp, deptwhere emp.deptno = dept.deptnoand dept.dname = ‘SALES’;

Those queries almost necessarily have to access the DEPT table and the EMP table using conventional indexes. We might use an index on DEPT.DNAME to find the SALES row(s) and retrieve the DEPTNO value for SALES, and then use an INDEX on EMP.DEPTNO to find the matching rows; however, by using a bitmap join index we can avoid all of that.

The bitmap join index allows us to index the DEPT.DNAME column, but have that index point not at the DEPT table, but at the EMP table. This is a pretty radical concept—to be able to index attributes from other tables—and it might change the way to implement your data model in a reporting system. You can, in effect, have your cake and eat it, too. You can keep your normalized data structures intact, yet get the benefits of denormalization at the same time.

Note how the beginning of the CREATE INDEX looks “normal” and creates the index INDEX_NAME on the table. But from there on, it deviates from “normal.” We see a reference to a column in the DEPT table: D.DNAME. We see a FROM clause, making this CREATE INDEX statement resemble a query. We have a join condition between multiple tables.

This CREATE INDEX statement indexes the DEPT.DNAME column, but in the context of the EMP table. If we ask those questions mentioned earlier, we would find the database never accesses the DEPT at all, and it need not do so because the DNAME column now exists in the index pointing to rows in the EMP table. For purposes of illustration, we will make the EMP and DEPT tables appear large (to avoid having the CBO think they are small and full scanning them instead of using indexes):
SQL> begindbms_stats.set_table_stats( user, ‘EMP’,
numrows => 1000000, numblks => 300000 );
dbms_stats.set_table_stats( user, ‘DEPT’,
numrows => 100000, numblks => 30000 );
dbms_stats.delete_index_stats( user, ‘EMP_BM_IDX’ ); end;/

Note You might be wondering why I invoked DELETE_INDEX_STATS earlier, it is because a CREATE INDEX automatically does a COMPUTE STATISTICS as it creates the index. Therefore, in this case, Oracle was “tricked”—it thinks it sees a table with 1,000,000 rows and a teeny tiny index on it (the table really only has 14 rows after all). The index statistics were accurate; the table statistics were “fake.” I needed to “fake” the index statistics as well—or I could have loaded the table up with 1,000,000 records before indexing it.

As you can see, to answer this particular question, we did not have to actually access either the EMP or DEPT table—the entire answer came from the index itself. All the information needed to answer the question was available in the index structure.

Further, we were able to skip accessing the DEPT table and, using the index on EMP that incorporated the data we needed from DEPT, gain direct access to the required rows:
SQL> select emp.* from emp, deptwhere emp.deptno = dept.deptnoand dept.dname = ‘SALES’;Execution Plan

Bitmap join indexes do have a prerequisite. The join condition must join to a primary or unique key in the other table. In the preceding example, DEPT.DEPTNO is the primary key of the DEPT table, and the primary key must be in place; otherwise, an error will occur:

ERROR at line 3:
ORA-25954: missing primary key or unique constraint on dimension