Example - AUTOMATIC LIST PARTITION v13

Suggest edits

The following example shows a (sales) table that uses an AUTOMATIC clause to create an automatic list partitioned table on the sales_state column. The database creates a new partition and adds data to a table.

CREATE TABLE sales
(
  dept_no      number,
  part_no      varchar2,
  sales_state  varchar2(20),
  date         date,
  amount       number
)
PARTITION BY LIST(sales_state) AUTOMATIC
(
  PARTITION P_CAL VALUES('CALIFORNIA'),
  PARTITION P_FLO VALUES('FLORIDA')
);

Query the ALL_TAB_PARTITIONS view to see an existing partition that is successfully created.

edb=# SELECT table_name, partition_name, high_value from ALL_TAB_PARTITIONS;
 table_name | partition_name |  high_value
------------+----------------+--------------
 SALES      | P_CAL          | 'CALIFORNIA'
 SALES      | P_FLO          | 'FLORIDA'
(2 rows)

Now, insert data into a sales table that cannot fit into an existing partition. For the regular list partitioned table, you will encounter an error but automatic list partitioning creates and inserts the data into a new partition.

edb=# INSERT INTO sales VALUES (1, 'IND', 'INDIANA');
INSERT 0 1
edb=# INSERT INTO sales VALUES (2, 'OHI', 'OHIO');
INSERT 0 1

Then, query the ALL_TAB_PARTITIONS view again after the insert. The partition is automatically created and data is inserted to hold a new value. A system-generated name of the partition is created that varies for each session.

edb=# SELECT table_name, partition_name, high_value from ALL_TAB_PARTITIONS;
 table_name | partition_name |  high_value
------------+----------------+--------------
 SALES      | P_CAL          | 'CALIFORNIA'
 SALES      | P_FLO          | 'FLORIDA'
 SALES      | SYS106900103   | 'INDIANA'
 SALES      | SYS106900104   | 'OHIO'
(4 rows)

← Prev

Example - PARTITION BY LIST

Example - PARTITION BY RANGE

Example - AUTOMATIC LIST PARTITION v13

← Prev

Next →