Tables and indexes can be split into multiple partitions and each partition has 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 column.
You can use partition extensions and subpartition extensions to perform some partition-level and subpartition-level operations. For example, you can delete all rows in a single partition or subpartition. Without an extension name, you must use a judgment statement (WHERE clause) to define the deletion scope. Judgment statements are not suitable for defining the partition-level operation scope in range partitioned tables and list partitioned tables, especially when the range partition key has multiple columns. Hash partitions and subpartitions are based on the hash function defined by the system. Therefore, judgment statements do not work well for them.
Partition extensions allow you to use partitions in the same way as you use a table. One advantage of this approach (most useful for range partitioned tables) is that you can establish an access control mechanism at the partition level 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 the view as a table.
Syntax
You can use the following SQL syntax to specify partition extensions and subpartition extensions.
The syntax for specifying a partition extension is as follows:
PARTITION partition
The syntax for specifying a subpartition extension is as follows:
SUBPARTITION subpartition
In DML statements INSERT, UPDATE, DELETE and ANALYZE, enclose the partition or subpartition extension in parentheses.
Limitations on extension names
When you use partition extensions and subpartition extensions, note the following limitations:
No synonyms are allowed in the name. Use the underlying table to specify a partition or subpartition extension name. Synonyms, views, or any other objects are not allowed.
In the
PARTITIONandSUBPARTITIONclauses, do not specify binding variables for a partition or subpartition name.
Examples
In the following example, the partitioned table sales has the sales_q1_2021 partition. Create a view for the partition, and then delete some rows from the partition.
/*Create the Q1_2021_sales view for the sales_q1_2000 partition.*/
CREATE VIEW Q1_2021_sales
AS
SELECT *
FROM sales PARTITION (SALES_Q1_2021);
/*Delete all values that meet the condition of amount_sold < 0 from the Q1_2021_sales view.*/
DELETE FROM Q1_2021_sales
WHERE amount_sold < 0;