Tables and indexes can be partitioned, and each partition has the same logical properties. For example, all partitions of a table share the same column and constraint definitions, and all partitions of an index share the same index column.
Users can use partition and subpartition extensions to perform operations at the partition or subpartition level. For example, delete all rows from a single partition or subpartition. If no extension name is specified, you need to use a WHERE clause to define the deletion range. Range-partitioned and list-partitioned tables are not suitable for defining partition-level operations using a WHERE clause, especially when the range partitioning key involves multiple columns. Hash-partitioned and subpartitioned tables are based on system-defined hash functions, making it difficult to use a WHERE clause.
Partition extensions allow you to use partitions as if they were tables. One advantage of this method (especially useful for range-partitioned tables) is that you can build partition-level access control mechanisms by granting or revoking privileges on these views to other users or roles. To use a partition as a table, you need to create a view by selecting data from the partition and then use that view as a table.
Syntax
Users can specify the partition extension table name and subpartition extension table name using the following SQL syntax.
The syntax for a partition extension table name is as follows:
PARTITION partition
The syntax for a subpartition extension table name is as follows:
SUBPARTITION subpartition
The INSERT, UPDATE, DELETE, and ANALYZE DML statements require parentheses around the partition or subpartition name.
Limitations on extension names
When using partition extension table names and subpartition extension table names, the following limitations apply:
Synonyms are not allowed. You must use the base table to specify the partition or subpartition extension name. Synonyms, views, or any other objects cannot be used.
In the
PARTITIONandSUBPARTITIONclauses, you cannot specify a bound variable for the partition or subpartition name.
Examples
In the following example, the partitioned table sales has a partition named sales_q1_2021, and a view is created for it. Then, some rows are deleted from the partition.
/*Create a view named Q1_2021_sales for the partition sales_q1_2000*/
CREATE VIEW Q1_2021_sales
AS
SELECT *
FROM sales PARTITION (SALES_Q1_2021);
/*Delete rows from the view Q1_2021_sales where amount_sold < 0*/
DELETE FROM Q1_2021_sales
WHERE amount_sold < 0;