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.
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 partition | PARTITION FOR ( partition_key_value [, partition_key_value]... )
SUBPARTITION subpartition | SUBPARTITION FOR ( subpartition_key_value [, subpartition_key_value]... )
Partition or subpartition names in data manipulation language (DML) statements, such as
ANALYZE, must be included in parentheses (). This small distinction is reflected in the
partition_extension_clause element: In
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.
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.
SUBPARTITION FORclauses, you cannot specify the
MAXVALUEkeyword, or a bind variable for
SUBPARTITIONclauses, you cannot specify a bind variable for the partition or subpartition name.
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;