Home > Oracle > Oracle: Bitmap-Join Indexes

Oracle: Bitmap-Join Indexes

Oracle 9i has a special type of Bitmap index called Bitmap-Join Indexes .
As with other Bitmap indexes, it will cause table locks, so use this only on read-only or OLAP tables.

It looks like this

CREATE BITMAP INDEX emp_dept_bj1
ON emp(d.dname)
FROM emp e
    ,dept d
WHERE e.deptno = d.deptno;

A variant is to substitute the WHERE keyword with ON.

CREATE BITMAP INDEX emp_dept_bj1
ON emp(d.dname)
FROM emp e
    ,dept d
ON e.deptno = d.deptno

It only works in Oracle-style syntax. ANSI-style SQL will yield ORA-00921 error:

CREATE BITMAP INDEX emp_dept_bj1
ON emp(d.dname)
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno

ORA-00921: unexpected end of SQL command

For clarity, here is how the emp and dept tables look like.

SQL> SELECT * FROM emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 12/17/1980     800.00               20
 7499 ALLEN      SALESMAN   7698 02/20/1981    1600.00    300.00     30
....
 7934 MILLER     CLERK      7782 01/23/1982    1300.00               10

14 rows selected

SQL> SELECT * FROM dept;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 SALES          DALLAS
    30 RESEARCH       CHICAGO
    40 OPERATIONS     BOSTON

4 rows selected
About these ads
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: