CREATE TABLE mod_sales_partitioning 
  ( prod_id       NUMBER        NOT NULL, 
    cust_id       NUMBER        NOT NULL, 
    time_id       DATE          NOT NULL, 
    channel_id    NUMBER        NOT NULL, 
    promo_id      NUMBER        NOT NULL, 
    quantity_sold NUMBER(10,2)  NOT NULL, 
    amount_sold   NUMBER(10,2)  NOT NULL 
  ) 
  PARTITION BY RANGE (time_id) 
  (PARTITION sales_q1_2017 VALUES LESS THAN (TO_DATE('01-APR-2017','dd-MON-yyyy')), 
   PARTITION sales_q2_2017 VALUES LESS THAN (TO_DATE('01-JUL-2017','dd-MON-yyyy')), 
   PARTITION sales_q3_2017 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy')), 
   PARTITION sales_q4_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy')) 
 )

CREATE INDEX i1_cust_id_indx ON mod_sales_partitioning (cust_id) LOCAL

CREATE INDEX i2_time_id_indx ON mod_sales_partitioning (time_id)

CREATE INDEX i3_prod_id_indx ON mod_sales_partitioning (prod_id)

CREATE INDEX i4_cust_id_indx ON mod_sales_partitioning (cust_id) INVISIBLE

CREATE INDEX i5_prod_id_cust_id_indx ON mod_sales_partitioning (prod_id, cust_id) LOCAL

CREATE INDEX i6_prod_id_cust_id_indx ON mod_sales_partitioning (prod_id, cust_id) INVISIBLE

CREATE INDEX i7_cust_id_indx ON mod_sales_partitioning (cust_id) 
   GLOBAL PARTITION BY HASH (cust_id) 
     PARTITIONS 2 
   INVISIBLE

CREATE INDEX i8_prod_id_cust_id_indx ON mod_sales_partitioning (prod_id, cust_id) 
   GLOBAL PARTITION BY HASH (prod_id) 
     PARTITIONS 2 
   INVISIBLE

select index_name, index_type, partitioned, visibility 
from user_indexes 
where table_name = 'MOD_SALES_PARTITIONING' 
order by index_name 

select index_name, partitioning_type, subpartitioning_type, locality 
from user_part_indexes 
where table_name = 'MOD_SALES_PARTITIONING' 
order by index_name 

ALTER TABLE mod_sales_partitioning 
 MODIFY  
 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id) 
  SUBPARTITIONS 8 
 ( PARTITION  sales_q1_2017 VALUES LESS THAN (TO_DATE('01-APR-2017','dd-MON-yyyy')), 
    PARTITION sales_q2_2017 VALUES LESS THAN (TO_DATE('01-JUL-2017','dd-MON-yyyy')), 
    PARTITION sales_q3_2017 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy')), 
    PARTITION sales_q4_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy'))) 
    ONLINE