Example - Setting an AUTOMATIC List Partition v13

Suggest edits

The example that follows modifies a table sales to use automatic list partition instead of regular list partitioning. Use the following command to create a sales table:

CREATE TABLE sales
(
  dept_no      number,
  part_no      varchar2,
  sales_state  varchar2(20),
  date         date,
  amount       number
)
PARTITION BY LIST(sales_state)
(
  PARTITION P_KAN VALUES('KANSAS'),
  PARTITION P_TEX VALUES('TEXAS')
);

To implement automatic list partitioning on the sales table, invoke the following command:

ALTER TABLE sales SET AUTOMATIC;

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

edb=# SELECT table_name, partition_name, high_value FROM ALL_TAB_PARTITIONS;
 table_name | partition_name | high_value
------------+----------------+------------
 SALES      | P_KAN          | 'KANSAS'
 SALES      | P_TEX          | 'TEXAS'
(2 rows)

Now, insert data into the sales table to create a new partition and add the new value.

edb=# INSERT INTO sales VALUES (1, 'VIR', 'VIRGINIA');
INSERT 0 1

Then, query the ALL_TAB_PARTITIONS view again after the insert. The automatic list partition is successfully created and data is inserted. 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_KAN          | 'KANSAS'
 SALES      | P_TEX          | 'TEXAS'
 SALES      | SYS106900103   | 'VIRGINIA'
(3 rows)

← Prev

ALTER TABLE...SET [ PARTITIONING ] AUTOMATIC

ALTER TABLE...SET SUBPARTITION TEMPLATE

Example - Setting an AUTOMATIC List Partition v13

← Prev

Next →