Tables and indexes can be partitioned into multiple partitions, each with 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 columns.
Users can use partition and subpartition extensions to perform certain partition-level and subpartition-level operations. For example, delete all rows from a single partition or subpartition. If no extension name is provided, a WHERE clause must be used to define the deletion scope. Range-partitioned and list-partitioned tables are not suitable for defining partition-level operations using a WHERE clause, especially when the range partitioning key consists of multiple columns. Hash-partitioned and subpartitioned tables, on the other hand, 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 a single 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 of extension names
When using partition extension table names and subpartition extension table names, the following limitations apply:
Synonyms are not allowed in the names. 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 bind 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 values from the view Q1_2021_sales where amount_sold < 0.*/
DELETE FROM Q1_2021_sales
WHERE amount_sold < 0;