Create Bitmap Index

Create Bitmap Index example

  

CREATE TABLESPACE tbs_01
  DATAFILE 'tbs_01.dat' 
    SIZE 20M
  ONLINE;
   
CREATE TABLESPACE tbs_02
  DATAFILE 'tbs_02.dat' 
    SIZE 20M
  ONLINE;

CREATE TABLESPACE tbs_03
  DATAFILE 'tbs_03.dat' 
    SIZE 20M
  ONLINE;  
   
-- create table Hash-Partitioned Table   
CREATE TABLE test_course 
    ( course_id          NUMBER(9)   PRIMARY KEY,
      course_name        VARCHAR2(250),
	  price				 NUMBER) 
 PARTITION BY HASH (course_id) 
 PARTITIONS 3
 STORE IN (tbs_01, tbs_02, tbs_03);
 
-- create Bitmap Index   
CREATE BITMAP INDEX course_tab_idx 
   ON test_course(price)
   LOCAL(PARTITION ix_p1 TABLESPACE tbs_01,
         PARTITION ix_p2,
         PARTITION ix_p3 TABLESPACE tbs_02)
   TABLESPACE tbs_03;

Output:

tablespace TBS_01 created.
tablespace TBS_02 created.

tablespace TBS_03 created.

table TEST_COURSE created.
bitmap index COURSE_TAB_IDX created.

Check indexes

select INDEX_NAME, INDEX_TYPE, PARTITIONED
from USER_INDEXES
WHERE table_name='TEST_COURSE';   

Output:

INDEX_NAMEINDEX_TYPEPARTITIONED
COURSE_TAB_IDXBITMAPYES
SYS_C009932NORMALNO