Oracle Live SQL - Script: Creating a Composite Range-List Partitioned Table

Statement 1

The table is created with composite range-list partitioning. Composite range-list partitioning partitions data using the range method, and within each partition, subpartitions the data further using the list method. The range partition uses the value of sales_date column and list subpartition uses the value of the state_code column. For example, a value for sales_date that is less than 01-OCT-2014 with a value for state_code that is equal to CT would be stored in the sales_q3_region_east_2014 partition.

Create the QUARTERLY_REGIONAL_SALES table

CREATE TABLE quarterly_regional_sales ( 
    product_id       NUMBER(6), 
    customer_id      NUMBER, 
    channel_id       CHAR(1), 
    promo_id         NUMBER(6), 
    sale_date        DATE, 
    quantity_sold    INTEGER, 
    amount_sold      NUMBER(10,2), 
    store_name       VARCHAR(30), 
    state_code       VARCHAR(2) 
) 
 
  PARTITION BY RANGE (sale_date) 
  SUBPARTITION BY LIST (state_code) 
 
 (PARTITION sales_q1_2014 VALUES LESS THAN (TO_DATE('01-APR-2014','dd-MON-yyyy')) 
 
         (SUBPARTITION sales_q1_region_east_2014  
             VALUES ('CT','MA','MD','ME','NH','NJ','NY','PA','VA'), 
           SUBPARTITION sales_q1_region_west_2014 
             VALUES ('AZ','CA','CO','NM','NV','OR','UT','WA'), 
           SUBPARTITION sales_q1_region_south_2014 
             VALUES ('AL','AR','GA','KY','LA','MS','TN','TX'), 
           SUBPARTITION sales_q1_region_central_2014 
             VALUES ('IA','IL','KS','MI','MO','ND','OH','OK','SD'), 
           SUBPARTITION sales_q1_region_other_2014  
             VALUES ('HI','PR'), 
           SUBPARTITION sales_q1_region_null_2014  
             VALUES (NULL), 
           SUBPARTITION  
             VALUES (DEFAULT) 
          ), 
 
  PARTITION sales_q2_2014 VALUES LESS THAN (TO_DATE('01-JUL-2014','dd-MON-yyyy')) 
 
         (SUBPARTITION sales_q2_region_east_2014  
             VALUES ('CT','MA','MD','ME','NH','NJ','NY','PA','VA'), 
           SUBPARTITION sales_q2_region_west_2014 
             VALUES ('AZ','CA','CO','NM','NV','OR','UT','WA'), 
           SUBPARTITION sales_q2_region_south_2014 
             VALUES ('AL','AR','GA','KY','LA','MS','TN','TX'), 
           SUBPARTITION sales_q2_region_central_2014 
             VALUES ('IA','IL','KS','MI','MO','ND','OH','OK','SD'), 
           SUBPARTITION sales_q2_region_other_2014  
             VALUES ('HI','PR'), 
           SUBPARTITION sales_q2_region_null_2014  
             VALUES (NULL), 
           SUBPARTITION  
             VALUES (DEFAULT) 
          ), 
 
 
  PARTITION sales_q3_2014 VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy')) 
 
         (SUBPARTITION sales_q3_region_east_2014  
             VALUES ('CT','MA','MD','ME','NH','NJ','NY','PA','VA'), 
           SUBPARTITION sales_q3_region_west_2014 
             VALUES ('AZ','CA','CO','NM','NV','OR','UT','WA'), 
           SUBPARTITION sales_q3_region_south_2014 
             VALUES ('AL','AR','GA','KY','LA','MS','TN','TX'), 
           SUBPARTITION sales_q3_region_central_2014 
             VALUES ('IA','IL','KS','MI','MO','ND','OH','OK','SD'), 
           SUBPARTITION sales_q3_region_other_2014  
             VALUES ('HI','PR'), 
           SUBPARTITION sales_q3_region_null_2014  
             VALUES (NULL), 
           SUBPARTITION  
             VALUES (DEFAULT) 
          ), 
 
 
  PARTITION sales_q4_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','dd-MON-yyyy')) 
 
         (SUBPARTITION sales_q4_region_east_2014  
             VALUES ('CT','MA','MD','ME','NH','NJ','NY','PA','VA'), 
           SUBPARTITION sales_q4_region_west_2014 
             VALUES ('AZ','CA','CO','NM','NV','OR','UT','WA'), 
           SUBPARTITION sales_q4_region_south_2014 
             VALUES ('AL','AR','GA','KY','LA','MS','TN','TX'), 
           SUBPARTITION sales_q4_region_central_2014 
             VALUES ('IA','IL','KS','MI','MO','ND','OH','OK','SD'), 
           SUBPARTITION sales_q4_region_other_2014  
             VALUES ('HI','PR'), 
           SUBPARTITION sales_q4_region_null_2014  
             VALUES (NULL), 
           SUBPARTITION  
             VALUES (DEFAULT) 
          ), 
 
 
  PARTITION sales_q1_2015 VALUES LESS THAN (TO_DATE('01-APR-2015','dd-MON-yyyy')) 
 
         (SUBPARTITION sales_q1_region_east_2015  
             VALUES ('CT','MA','MD','ME','NH','NJ','NY','PA','VA'), 
           SUBPARTITION sales_q1_region_west_2015 
             VALUES ('AZ','CA','CO','NM','NV','OR','UT','WA'), 
           SUBPARTITION sales_q1_region_south_2015 
             VALUES ('AL','AR','GA','KY','LA','MS','TN','TX'), 
           SUBPARTITION sales_q1_region_central_2015 
             VALUES ('IA','IL','KS','MI','MO','ND','OH','OK','SD'), 
           SUBPARTITION sales_q1_region_other_2015  
             VALUES ('HI','PR'), 
           SUBPARTITION sales_q1_region_null_2015 
             VALUES (NULL), 
           SUBPARTITION  
             VALUES (DEFAULT) 
          ) 
 
 
)

Table created.

Statement 2

Display the partitions in the table with this SQL query.

SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='QUARTERLY_REGIONAL_SALES'

 
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
QUARTERLY_REGIONAL_SALES SALES_Q1_2014 1 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
QUARTERLY_REGIONAL_SALES SALES_Q2_2014 2 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
QUARTERLY_REGIONAL_SALES SALES_Q3_2014 3 TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
QUARTERLY_REGIONAL_SALES SALES_Q4_2014 4 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
QUARTERLY_REGIONAL_SALES SALES_Q1_2015 5 TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

5 rows selected.

TABLE_NAME	PARTITION_NAME	PARTITION_POSITION	HIGH_VALUE
QUARTERLY_REGIONAL_SALES	SALES_Q1_2014	1	TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
QUARTERLY_REGIONAL_SALES	SALES_Q2_2014	2	TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
QUARTERLY_REGIONAL_SALES	SALES_Q3_2014	3	TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
QUARTERLY_REGIONAL_SALES	SALES_Q4_2014	4	TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
QUARTERLY_REGIONAL_SALES	SALES_Q1_2015	5	TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Statement 3

Insert values into the table.

INSERT INTO quarterly_regional_sales VALUES (1001,100,'A',150,'10-FEB-2014',500,2000,'My Store MA','MA')

1 row(s) inserted.

Statement 4

Insert values into the table.

INSERT INTO quarterly_regional_sales VALUES (1002,110,'B',180,'15-JUN-2014',100,1000,'My Store OK','OK')

1 row(s) inserted.

Statement 5

Insert values into the table.

INSERT INTO quarterly_regional_sales VALUES (1001,100,'A',150,'20-AUG-2014',500,2000,'My Store MA','MA')

1 row(s) inserted.

Statement 6

Insert values into the table.

INSERT INTO quarterly_regional_sales VALUES (1002,110,'B',180,'15-SEP-2014',100,1000,'My Store OK','OK')

1 row(s) inserted.

Statement 7

Display all the data in the table.

SELECT * FROM quarterly_regional_sales

 
PRODUCT_ID CUSTOMER_ID CHANNEL_ID PROMO_ID SALE_DATE QUANTITY_SOLD AMOUNT_SOLD STORE_NAME STATE_CODE
1001 100 A 150 10-FEB-14 500 2000 My Store MA MA
1002 110 B 180 15-JUN-14 100 1000 My Store OK OK
1001 100 A 150 20-AUG-14 500 2000 My Store MA MA
1002 110 B 180 15-SEP-14 100 1000 My Store OK OK

4 rows selected.

PRODUCT_ID	CUSTOMER_ID	CHANNEL_ID	PROMO_ID	SALE_DATE	QUANTITY_SOLD	AMOUNT_SOLD	STORE_NAME	STATE_CODE
1001	100	A	150	10-FEB-14	500	2000	My Store MA	MA
1002	110	B	180	15-JUN-14	100	1000	My Store OK	OK
1001	100	A	150	20-AUG-14	500	2000	My Store MA	MA
1002	110	B	180	15-SEP-14	100	1000	My Store OK	OK

Statement 8

Display data from a specified partition in the table.

SELECT * FROM quarterly_regional_sales PARTITION(sales_q3_2014)

 
PRODUCT_ID CUSTOMER_ID CHANNEL_ID PROMO_ID SALE_DATE QUANTITY_SOLD AMOUNT_SOLD STORE_NAME STATE_CODE
1001 100 A 150 20-AUG-14 500 2000 My Store MA MA
1002 110 B 180 15-SEP-14 100 1000 My Store OK OK

2 rows selected.

PRODUCT_ID	CUSTOMER_ID	CHANNEL_ID	PROMO_ID	SALE_DATE	QUANTITY_SOLD	AMOUNT_SOLD	STORE_NAME	STATE_CODE
1001	100	A	150	20-AUG-14	500	2000	My Store MA	MA
1002	110	B	180	15-SEP-14	100	1000	My Store OK	OK

Statement 9

Display data from a specified subpartition in the table. When you are finished testing the example, you can clean up the environment by dropping the table (DROP TABLE quarterly_regional_sales).

SELECT * FROM quarterly_regional_sales SUBPARTITION(sales_q3_region_east_2014)

 
PRODUCT_ID CUSTOMER_ID CHANNEL_ID PROMO_ID SALE_DATE QUANTITY_SOLD AMOUNT_SOLD STORE_NAME STATE_CODE
1001 100 A 150 20-AUG-14 500 2000 My Store MA MA

Creating a Composite Range-List Partitioned Table

Additional Information