Create Index on a Hash-Partitioned Table

Create Index on a Hash-Partitioned Table example

  

CREATE TABLESPACE tbs_01
  DATAFILE 'tbs_01.dat' 
    SIZE 20M
  ONLINE;
   
CREATE TABLESPACE tbs_02
  DATAFILE 'tbs_02.dat' 
    SIZE 20M
  ONLINE;
   
-- create Hash-Partitioned Table   
CREATE TABLE test_course 
    ( course_id          NUMBER(6)   PRIMARY KEY,
      course_name        VARCHAR2(250)) 
 PARTITION BY HASH (course_id) 
 PARTITIONS 2 
 STORE IN (tbs_01, tbs_02);
 
-- create Index on a Hash-Partitioned Table    
CREATE INDEX test_course_idx 
  ON test_course(course_name) LOCAL 
  STORE IN (tbs_01, tbs_02); 

Output:

tablespace TBS_01 created.
tablespace TBS_02 created.

table TEST_COURSE created.
index TEST_COURSE_IDX created.

Check indexes

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

Output: