Bitmap join indexes - An Simple Example

Bitmap join indexes are a way to denormalize without denormalizing.

consider the emp/dept tables..

people ask "how many people work in RESEARCH?". "Who in SALES makes more than 5000$?"

Note that they do not ask "how many people work in deptno=10", "who in deptno=20 makes more than
5000$"

so, do you move DNAME into EMP? No, you can create a bitmap join index. consider:


ops$tkyte@ORA10G> create table emp as select * from scott.emp;
Table created.

ops$tkyte@ORA10G> create table dept as select * from scott.dept;
Table created.

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter table dept add constraint dept_pk primary key(deptno);

Table altered.

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set echo off
Enter to continue

so, where is how to create the bitmap index -- using a FROM
and WHERE clause

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> 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.

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set echo off
Enter to continue


of course, to use a bitmap index we must use the CBO, we would need
lots of data to get it to want to use the bitmap as well -- so we fake
it out.


ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec dbms_stats.set_table_stats( user, 'EMP', numrows => 1000000, numblks =>
300000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> exec dbms_stats.set_table_stats( user, 'DEPT', numrows => 100000, numblks =>
30000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set echo off
Enter to continue

and now we just query...


ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set autotrace on
ops$tkyte@ORA10G> select count(*)
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and dept.dname = 'SALES'
5 /

COUNT(*)
----------
6


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT) (Cost=1 Card=10000 Bytes=130000)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'EMP_BM_IDX' (INDEX (BITMAP))

Note how neither of EMP or DEPT was actually accessed for this query...


ops$tkyte@ORA10G> select emp.*
2 from emp, dept
3 where emp.deptno = dept.deptno
4 and dept.dname = 'SALES'
5 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ------- --------- ------ ------- ------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30

6 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8712 Card=10000 Bytes=870000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=8712 Card=10000 Bytes=870000)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'EMP_BM_IDX' (INDEX (BITMAP))

and notice how DEPT is not accessed here either


bitmap join indexes could be used in some cases INSTEAD of a star transformation -- it is not that
they "help it", they are an alternative in a way.