Example: CREATING UNIQUE INDEX on PARTITION TABLE v17

Suggest edits

For unique-index partitioned tables, you can use the range, list, or hash-partitioning method. To create a unique ROWID for the partitioned table, you must set the parameter default_with_rowids = true.

This example creates a partitioned table sales using the PARTITION BY LIST clause. The sales table stores information in three partitions: europe, asia, and americas.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA') 
) 
WITH(ROWIDS=TRUE);

The table is partitioned by the value specified in the country column:

edb=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
Output
 partition_name |     high_value      
----------------+---------------------
 EUROPE         | 'FRANCE', 'ITALY'
 ASIA           | 'INDIA', 'PAKISTAN'
 AMERICAS       | 'US', 'CANADA'
(3 rows)

Insert values into the sales table:

INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');
INSERT INTO sales VALUES (10, '9519b', 'ITALY', '20-Aug-2012', '700000');

This query shows that a unique index is created for the sales table:

edb=# SELECT oid, relname, relhasindex from pg_class where relname='sales';
Output
  oid  | relname | relhasindex 
-------+---------+-------------
 16557 | sales   | f
(1 row)

Querying the contents of the sales_europe confirms that the unique index is created for the table:

edb=# SELECT oid, relname, relhasindex from pg_class where relname='sales_europe';
Output
  oid  |   relname    | relhasindex 
-------+--------------+-------------
 16561 | sales_europe | t
(1 row)
edb=# SELECT * from pg_index where indrelid='16561';
Output
 indexrelid | indrelid | indnatts | indnkeyatts | indisunique | indisprimary | indisexclusion | indimmediate | indisclustered | indisvalid | indcheckxmin | indisready | indislive | indisreplident |
 indkey | indcollation | indclass | indoption | indexprs | indpred 
------------+----------+----------+-------------+-------------+--------------+----------------+--------------+----------------+------------+--------------+------------+-----------+----------------+
--------+--------------+----------+-----------+----------+---------
      16565 |    16561 |        1 |           1 | t           | f            | f              | t            | f              | t          | f            | t          | t         | f              |
 1      | 0            | 3124     | 0         |          | 
(1 row)
← Prev

Example: PARTITION BY RANGE, SUBPARTITION BY LIST

↑ Up

CREATE TABLE...PARTITION BY

Next →

Example: CREATING SUBPARTITION TEMPLATE