Indexes - Expert Oracle Database Architecture - page 467

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:
select count(*)
from emp, dept
where emp.deptno = dept.deptno
and dept.dname = 'SALES'
/
select emp.*
from emp, dept
where emp.deptno = dept.deptno
and 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.
Here's the index we would create for this example:
EODA@ORA12CR1> create bitmap index emp_bm_idx
2 on emp( d.dname )
3 from emp e, dept d
4 where e.deptno = d.deptno
5 /
Index created.
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):
EODA@ORA12CR1> begin
2 dbms_stats.set_table_stats( user, 'EMP',
3 numrows => 1000000, numblks => 300000 );
4 dbms_stats.set_table_stats( user, 'DEPT',
5 numrows => 100000, numblks => 30000 );
6 dbms_stats.delete_index_stats( user, 'EMP_BM_IDX' );
7 end;
8 /
PL/SQL procedure successfully completed.