All Products
Search
Document Center

Reference partitioned tables and indexes

Last Updated: Jun 18, 2021

You can partition tables and indexes. When you partition a table, these schema objects consist of a number of parts called partitions. All the partitions have the same logical attributes. For example, all partitions in a table share the same column and constraint definitions, whereas all partitions in an index share the same index columns.

Partition-extended and subpartition-extended names allow you to perform some partition-level and subpartition-level operations. For example, you can delete all rows from only one partition or subpartition. To manage partitions or subpartitions that do not have extended names, specify a range by using the WHERE clause. For range-partitioned and list-partitioned tables, you may encounter difficulties when you attempt to use conditional statements to phrase a partition-level operation. The difficulty increases if multiple columns are used as the range partition keys. Hash partitions and subpartitions are based on system-defined hash functions. Therefore, you may encounter more difficulties in using conditional statements on these partitions and subpartitions.

Partition-extended names allow you to use partitions in the same way as you use tables. This method allows you to build partition-level access control mechanisms by granting (or revoking) permissions on these views to (or from) other users or roles. This feature is useful when you query data in range-partitioned tables. To use a partition as a table, create a view by selecting data from the partition, and then use the view as a table.

Syntax

If the partition_extended_name or subpartition_extended_name element appears in the syntax of an SQL statement, you can use these elements to specify partition-extended or subpartition-extended table names. Syntax of partition_extended_name:

PARTITION partition
| 
PARTITION FOR ( partition_key_value [, partition_key_value]... )

Syntax of subpartition_extended_name:

SUBPARTITION subpartition
| 
SUBPARTITION FOR ( subpartition_key_value [, subpartition_key_value]... )

Partition or subpartition names in data manipulation language (DML) statements, such as INSERT, UPDATE, DELETE, and ANALYZE, must be included in parentheses (). This small distinction is reflected in the partition_extension_clause element: In partition_extended_name, subpartition_extended_name, and partition_extension_clause, the PARTITION FOR and SUBPARTITION FOR clauses allow you to reference partitions without using their names. They are valid for all partition types, and are especially useful for interval partitions. When you insert data into a table, interval partitions are automatically created as needed.

For each partition_key_value or subpartition_key_value of the preceding elements, a value is specified for each partition key column. If multiple columns are used as partition keys, you must specify a value for each partition key. If a composite partitioning scheme is used, you must specify a value for each subpartition key after you specify a value for a partition key. Separate all partition key values with commas (,). If an interval partitioning scheme is used, you can specify only one partition_key_value. The specified value must be a valid value of the NUMBER or a date and time data type. Your SQL statements operate on the partitions or subpartitions that contain the specified values.

Limits on extended names

The use of partition-extended and subpartition-extended table names is subject to the following limits:

  • No remote tables in names: Partition-extended and subpartition-extended table names cannot contain database links or synonyms that can be translated to tables that contain database links. To use remote partitions and subpartitions, create a view at the remote site by using the extended table name syntax and then reference the remote view.

  • No synonyms in names: You must use base tables to specify partition-extended and subpartition-extended names. You cannot use synonyms, views, or other objects.

  • In the PARTITION FOR and SUBPARTITION FOR clauses, you cannot specify the DEFAULT or MAXVALUE keyword, or a bind variable for partition_key_value or subpartition_key_value.

  • In the PARTITION and SUBPARTITION clauses, you cannot specify a bind variable for the partition or subpartition name.

Examples

In the following example, sales is a partitioned table with the sales_q1_2000 partition. You can execute the following statement to create a view of the sales_q1_2000 partition, and then use the view in the same way as you use a table. In this example, some rows are deleted from the partition.

/*Create the Q1_2000_sales view for the sales_q1_2000 partition.*/
CREATE VIEW Q1_2000_sales
AS
SELECT *
FROM sales PARTITION (SALES_Q1_2000);
/*Delete the record in which the value in the amount_sold column is less than 0 from the Q1_2000_sales view.*/
DELETE FROM Q1_2000_sales
WHERE amount_sold < 0;