CREATE TABLE sales_by_region (
item_id INTEGER,
item_quantity INTEGER,
store_name VARCHAR(30),
state_code VARCHAR(2),
sale_date DATE)
PARTITION BY LIST (state_code)
(
PARTITION region_east
VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ'),
PARTITION region_west
VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO'),
PARTITION region_south
VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
PARTITION region_central
VALUES ('OH','ND','SD','MO','IL','MI','IA')
)
Table created.
SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION'
| TABLE_NAME | PARTITION_NAME | PARTITION_POSITION | HIGH_VALUE |
|---|---|---|---|
| SALES_BY_REGION | REGION_EAST | 1 | 'MA', 'NY', 'CT', 'NH', 'ME', 'MD', 'VA', 'PA', 'NJ' |
| SALES_BY_REGION | REGION_WEST | 2 | 'CA', 'AZ', 'NM', 'OR', 'WA', 'UT', 'NV', 'CO' |
| SALES_BY_REGION | REGION_SOUTH | 3 | 'TX', 'KY', 'TN', 'LA', 'MS', 'AR', 'AL', 'GA' |
| SALES_BY_REGION | REGION_CENTRAL | 4 | 'OH', 'ND', 'SD', 'MO', 'IL', 'MI', 'IA' |
ALTER TABLE sales_by_region
ADD PARTITION region_nonmainland VALUES ('HI', 'PR')
Table altered.
ALTER TABLE sales_by_region
ADD PARTITION region_null VALUES (NULL)
Table altered.
ALTER TABLE sales_by_region
ADD PARTITION VALUES (DEFAULT)
Table altered.
SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION'
| TABLE_NAME | PARTITION_NAME | PARTITION_POSITION | HIGH_VALUE |
|---|---|---|---|
| SALES_BY_REGION | REGION_EAST | 1 | 'MA', 'NY', 'CT', 'NH', 'ME', 'MD', 'VA', 'PA', 'NJ' |
| SALES_BY_REGION | REGION_WEST | 2 | 'CA', 'AZ', 'NM', 'OR', 'WA', 'UT', 'NV', 'CO' |
| SALES_BY_REGION | REGION_SOUTH | 3 | 'TX', 'KY', 'TN', 'LA', 'MS', 'AR', 'AL', 'GA' |
| SALES_BY_REGION | REGION_CENTRAL | 4 | 'OH', 'ND', 'SD', 'MO', 'IL', 'MI', 'IA' |
| SALES_BY_REGION | REGION_NONMAINLAND | 5 | 'HI', 'PR' |
| SALES_BY_REGION | REGION_NULL | 6 | NULL |
| SALES_BY_REGION | SYS_P449 | 7 | DEFAULT |
ALTER TABLE sales_by_region
MODIFY PARTITION region_south
ADD VALUES ('OK', 'KS')
Table altered.
INSERT INTO sales_by_region VALUES (1001,100,'My Store MA','MA','25-AUG-2014')
1 row(s) inserted.
INSERT INTO sales_by_region VALUES (1002,200,'My Store OK','OK','26-AUG-2014')
1 row(s) inserted.
SELECT * FROM sales_by_region
| ITEM_ID | ITEM_QUANTITY | STORE_NAME | STATE_CODE | SALE_DATE |
|---|---|---|---|---|
| 1001 | 100 | My Store MA | MA | 25-AUG-14 |
| 1002 | 200 | My Store OK | OK | 26-AUG-14 |
SELECT * FROM sales_by_region PARTITION(region_east)
| ITEM_ID | ITEM_QUANTITY | STORE_NAME | STATE_CODE | SALE_DATE |
|---|---|---|---|---|
| 1001 | 100 | My Store MA | MA | 25-AUG-14 |