Creating a composite interval-hash partitioned table
CREATE TABLE sales_interval_hash
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4
(PARTITION before_2016 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy'))
)Table created.
Viewing the structure of the composite interval-hash partitioned table
SELECT SUBSTR(TABLE_NAME,1,32), SUBSTR(PARTITION_NAME,1,32), SUBSTR(SUBPARTITION_NAME,1,32) FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='SALES_INTERVAL_HASH'| SUBSTR(TABLE_NAME,1,32) | SUBSTR(PARTITION_NAME,1,32) | SUBSTR(SUBPARTITION_NAME,1,32) | SALES_INTERVAL_HASH | BEFORE_2016 | SYS_SUBP5102 | SALES_INTERVAL_HASH | BEFORE_2016 | SYS_SUBP5103 | SALES_INTERVAL_HASH | BEFORE_2016 | SYS_SUBP5104 | SALES_INTERVAL_HASH | BEFORE_2016 | SYS_SUBP5105 |
|---|
Inserting data into the table
INSERT INTO sales_interval_hash VALUES (2105, 101, '15-FEB-16', 'B', 19, 10, 300.00) 1 row(s) inserted.
INSERT INTO sales_interval_hash VALUES (2105, 102, '21-APR-16', 'C', 12, 100, 2000.00)
1 row(s) inserted.
INSERT INTO sales_interval_hash values (1200, 155, '31-MAY-16', 'D', 20, 300, 3600.00)1 row(s) inserted.
INSERT INTO sales_interval_hash values (1400, 165, '31-MAY-16', 'E', 15, 100, 4000.00)1 row(s) inserted.
INSERT INTO sales_interval_hash VALUES (2105, 125, '05-AUG-16', 'B', 16, 40, 8500.00)
1 row(s) inserted.
INSERT INTO sales_interval_hash VALUES (2105, 302, '15-OCT-16', 'A', 11, 75, 4350.00)1 row(s) inserted.
Viewing the data in the table
SELECT * FROM sales_interval_hash| PROD_ID | CUST_ID | TIME_ID | CHANNEL_ID | PROMO_ID | QUANTITY_SOLD | AMOUNT_SOLD | 2105 | 101 | 15-FEB-16 | B | 19 | 10 | 300 | 2105 | 102 | 21-APR-16 | C | 12 | 100 | 2000 | 1200 | 155 | 31-MAY-16 | D | 20 | 300 | 3600 | 1400 | 165 | 31-MAY-16 | E | 15 | 100 | 4000 | 2105 | 125 | 05-AUG-16 | B | 16 | 40 | 8500 | 2105 | 302 | 15-OCT-16 | A | 11 | 75 | 4350 |
|---|
Viewing the structure of the composite interval-hash partitioned table after data has been inserted
SELECT SUBSTR(TABLE_NAME,1,32), SUBSTR(PARTITION_NAME,1,32), SUBSTR(SUBPARTITION_NAME,1,32) FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='SALES_INTERVAL_HASH'| SUBSTR(TABLE_NAME,1,32) | SUBSTR(PARTITION_NAME,1,32) | SUBSTR(SUBPARTITION_NAME,1,32) | SALES_INTERVAL_HASH | BEFORE_2016 | SYS_SUBP5102 | SALES_INTERVAL_HASH | BEFORE_2016 | SYS_SUBP5103 | SALES_INTERVAL_HASH | BEFORE_2016 | SYS_SUBP5104 | SALES_INTERVAL_HASH | BEFORE_2016 | SYS_SUBP5105 | SALES_INTERVAL_HASH | SYS_P5110 | SYS_SUBP5106 | SALES_INTERVAL_HASH | SYS_P5110 | SYS_SUBP5107 | SALES_INTERVAL_HASH | SYS_P5110 | SYS_SUBP5108 | SALES_INTERVAL_HASH | SYS_P5110 | SYS_SUBP5109 | SALES_INTERVAL_HASH | SYS_P5115 | SYS_SUBP5111 | SALES_INTERVAL_HASH | SYS_P5115 | SYS_SUBP5112 | SALES_INTERVAL_HASH | SYS_P5115 | SYS_SUBP5113 | SALES_INTERVAL_HASH | SYS_P5115 | SYS_SUBP5114 | SALES_INTERVAL_HASH | SYS_P5120 | SYS_SUBP5116 | SALES_INTERVAL_HASH | SYS_P5120 | SYS_SUBP5117 | SALES_INTERVAL_HASH | SYS_P5120 | SYS_SUBP5118 | SALES_INTERVAL_HASH | SYS_P5120 | SYS_SUBP5119 | SALES_INTERVAL_HASH | SYS_P5125 | SYS_SUBP5121 | SALES_INTERVAL_HASH | SYS_P5125 | SYS_SUBP5122 | SALES_INTERVAL_HASH | SYS_P5125 | SYS_SUBP5123 | SALES_INTERVAL_HASH | SYS_P5125 | SYS_SUBP5124 | SALES_INTERVAL_HASH | SYS_P5130 | SYS_SUBP5126 | SALES_INTERVAL_HASH | SYS_P5130 | SYS_SUBP5127 | SALES_INTERVAL_HASH | SYS_P5130 | SYS_SUBP5128 | SALES_INTERVAL_HASH | SYS_P5130 | SYS_SUBP5129 |
|---|