In the following example, you will add a GENDER column
to the EMPLOYEES table and then create a bitmap index on it:
SQL> alter table hr.employees
2 add (gender char(1));
Table altered.
SQL> create bitmap index
2 hr.gender_bm_ix on hr.employees(gender);
Index created.
Using Bitmap Join Indexes
As of Oracle9i, you can create an enhanced type of bitmap index called a bitmap join index. A
bitmap join index is a bitmap index representing the join between two or more tables. For each
value of a column in the first table of the join, the bitmap join index stores the ROWIDs of the
corresponding rows in the other tables with the same value as the column in the first table.
Bitmap join indexes are an alternative to materialized views that contain a join condition; the
storage required for storing the related ROWIDs can be significantly lower than storing the result
of the view itself.
In this example, you find out that the HR department is frequently joining the EMPLOYEES
and DEPARTMENTS table on the DEPARTMENT_ID column. As an alternative to creating a
materialized view, you decide to create a bitmap join index. Here is the SQL command to create
the bitmap join index:
Chapter 16: Managing Large Databases 591
SQL> create bitmap index
2 hr.emp_dept_bj_ix on hr.employees(hr.departments.department_id)
3 from hr.employees, hr.departments
4 where hr.employees.department_id = hr.departments.department_id;
Index created.
Pages:
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870