CREATE TABLE sales_auto_list
(
salesman_id NUMBER(5) NOT NULL,
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20) NOT NULL,
sales_amount NUMBER(10),
sales_date DATE NOT NULL
)
PARTITION BY LIST (sales_state) AUTOMATIC
(PARTITION P_CAL VALUES ('CALIFORNIA')
)Table created.
SELECT TABLE_NAME, PARTITIONING_TYPE, AUTOLIST, PARTITION_COUNT FROM USER_PART_TABLES WHERE TABLE_NAME ='SALES_AUTO_LIST'| TABLE_NAME | PARTITIONING_TYPE | AUTOLIST | PARTITION_COUNT | SALES_AUTO_LIST | LIST | YES | 1 |
|---|
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_AUTO_LIST'| TABLE_NAME | PARTITION_NAME | HIGH_VALUE | SALES_AUTO_LIST | P_CAL | 'CALIFORNIA' |
|---|
INSERT INTO SALES_AUTO_LIST VALUES(021, 'Mary Smith', 'FLORIDA', 41000, TO_DATE ('21-DEC-2018','DD-MON-YYYY'))1 row(s) inserted.
INSERT INTO SALES_AUTO_LIST VALUES(032, 'Luis Vargas', 'MICHIGAN', 42000, TO_DATE ('31-DEC-2018','DD-MON-YYYY'))1 row(s) inserted.
SELECT TABLE_NAME, PARTITIONING_TYPE, AUTOLIST, PARTITION_COUNT FROM USER_PART_TABLES WHERE TABLE_NAME ='SALES_AUTO_LIST'| TABLE_NAME | PARTITIONING_TYPE | AUTOLIST | PARTITION_COUNT | SALES_AUTO_LIST | LIST | YES | 3 |
|---|
INSERT INTO SALES_AUTO_LIST VALUES(015, 'Simone Blair', 'CALIFORNIA', 45000, TO_DATE ('11-JAN-2019','DD-MON-YYYY'))1 row(s) inserted.
INSERT INTO SALES_AUTO_LIST VALUES(015, 'Simone Blair', 'OREGON', 38000, TO_DATE ('18-JAN-2019','DD-MON-YYYY'))1 row(s) inserted.
SELECT TABLE_NAME, PARTITIONING_TYPE, AUTOLIST, PARTITION_COUNT FROM USER_PART_TABLES WHERE TABLE_NAME ='SALES_AUTO_LIST'| TABLE_NAME | PARTITIONING_TYPE | AUTOLIST | PARTITION_COUNT | SALES_AUTO_LIST | LIST | YES | 4 |
|---|
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_AUTO_LIST'| TABLE_NAME | PARTITION_NAME | HIGH_VALUE | SALES_AUTO_LIST | P_CAL | 'CALIFORNIA' | SALES_AUTO_LIST | SYS_P46849 | 'FLORIDA' | SALES_AUTO_LIST | SYS_P46850 | 'MICHIGAN' | SALES_AUTO_LIST | SYS_P46851 | 'OREGON' |
|---|