Using System Partitioning
System Partitioning enables you to create a single table consisting of multiple physical partitions. System partitioning does not use partitioning keys. Instead, it creates the number of partitions specified. Therefore, the resulting partitions have no bounds (range), values (list), or a partitioning method.
Because there are no partitioning keys, you must explicitly map the distributed table rows to the destination partition. When inserting a row, for example, you must use the partition extended syntax to specify the partition to which a row must be mapped.
See Also:
Supporting SQL syntax in the Oracle Database SQL Language ReferenceAdvantages of System Partitioned Tables
The main advantages of system-partitioned tables is that it can be used to create and maintain tables that are equipartitioned with respect to another table. For example, this means that a dependent table could be created as a system-partitioned table, with the same number of partitions as the base table. It follows that such a system-partitioned table can be used to store index data for a domain index, with the following implications:
-
Pruning follows the base table pruning rules: when a partition is accessed in the base table, the corresponding partition can be accessed in the system-partitioned table.
-
DDLs of the base table can be duplicated on the system-partitioned table. Therefore, if a partition is dropped on the base table, the corresponding partition on the system-partitioned table is dropped automatically.
Implementing System Partitioning
This section describes how to implement system partitioning.
ADDCI4483Creating a System-Partitioned Table
Example 8-16 describes how to create a system-partitioned table with four partitions. Each partition can have different physical attributes.
ADDCI4484Example 8-16 Creating a System-Partitioned Table
CREATE TABLE SystemPartitionedTable (c1 integer, c2 integer) PARTITION BY SYSTEM ( PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2, PARTITION p3 TABLESPACE tbs_3, PARTITION p4 TABLESPACE tbs_4 );
Inserting Data into a System-Partitioned Table
Example 8-17 demonstrates how to insert data into a system-partitioned table. Both INSERT
and MERGE
statements (not shown here) must use the partition extended syntax to identify the partition to which the row should be added. The tuple (4,5)
could have been inserted into any of the four partitions created in Example 8-16. DATAOBJ_TO_PARTITION
can also be used, as demonstrated by Example 8-18.
ADDCI4486Example 8-17 Inserting Data into a System-Partitioned Table
INSERT INTO SystemPartitionedTable PARTITION (p1) VALUES (4,5);
ADDCI4487Example 8-18 Inserting Data into a System-Partitioned Table Using DATAOBJ_TO_PARTITION
INSERT INTO SystemPartitionedTable PARTITION (DATAOBJ_TO_PARTITION (base_table, :physical_partid)) VALUES (...);
Note that the first line of code shows how to insert data into a named partition, while the second line of code shows that data can also be inserted into a partition based on the partition's order. The support for bind variables, illustrated on the third code line, is important because it allows cursor sharing between INSERT
statements.
Deleting and Updating Data in a System-Partitioned Table
While delete and update operations do not require the partition extended syntax, Oracle recommends that you use it if at all possible. Because there is no partition pruning, the entire table is scanned to execute the operation if the partition-extended syntax is omitted. This highlights the fact that there is no implicit mapping between the rows and the partitions.
Supporting Operations with System-Partitioned Tables
The following operations continue to be supported by system partitioning:
-
Partition maintenance operations and other DDLs, with the exception of:
-
ALTER INDEX SPLIT PARTITION
-
ALTER TABLE SPLIT PARTITION
-
CREATE TABLE
(asSELECT
)
-
-
Creation of local indexes, with the exception of unique local indexes because they require a partitioning key
-
Creation of local bitmapped indexes
-
Creation of global indexes
-
All DML operations
-
INSERT AS SELECT
operations with partition extended syntax, as shown in Example 8-19:ADDCI4490Example 8-19 Inserting Data into a Particular Partition of a Table
INSERT INTO TableName PARTITION ( PartitionName| DATAOBJ_TO_PARTITION(base_table, :physical_partid)) AS SubQuery
The following operations are no longer supported by system partitioning because system partitioning does not use a partitioning method, and therefore does not distribute rows to partitions.
-
CREATE TABLE AS SELECT
An alternative approach is to first create the table, and then insert rows into each partition. -
INSERT INTO
TableName
AS
SubQuery
Running Partition Maintenance Operations
As an example, this section discusses an ALTER TABLE SPLIT PARTITION
routine issued for the base table of a domain index.
-
The system invokes the ODCIIndexUpdPartMetadata() method using the information about the partition being added or dropped; remember that a 1:2 split involves dropping of one partition and adding two new partitions.
-
The system invokes the ODCIStatsUpdPartStatistics() on the affected partitions.
-
The system drops the partition that has been split from all system-partition index and statistics storage tables.
-
The system adds two new partitions to the system-partitioned tables.
-
If the partition that is being split is empty, then one call to ODCIIndexAlter() rebuilds the split partition, and a second call to ODCIIndexAlter()rebuilds the newly added partition.
Altering Table Exchange Partitions with Indexes
The ALTER TABLE EXCHANGE PARTITION
command is allowed for tables with domain indexes only under the following circumstances:
-
a domain index is defined on both the non-partitioned table, and the partitioned table
-
both the non-partitioned table and the partitioned table have the same associated indextype
The ALTER TABLE EXCHANGE PARTITION
routine invokse the following user-implemented methods:
-
ODCIIndexExchangePartition() for the affected partition and index
-
ODCIStatsExchangePartition() for the affected partition and index if statistics are collected for them
http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/dom_idx.htm#ADDCI2801
OCP 053:
245.Which statements are true regarding system-partitioned tables? (Choose all that apply.)
A. Only a single partitioning key column can be specified.
B. All DML statements must use partition-extended syntax.
C. The same physical attributes must be specified for each partition.
D. Unique local indexes cannot be created on a system-partitioned table.
E. Traditional partition pruning and partitionwise joins are not supported on these tables.
Answer: DE
答案解析:
参考:http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/dom_idx.htm#ADDCI4490
从以下官方文档得知,ABC是错误的,D是正确的,排除法,选DE。
题问:哪一个是关于系统分区表的真实陈述?
A.只有一个分区键列可以被指定。System partitioning does not use partitioning keys。
B.所有DML语句必须使用分区扩展语法。错误。
Both INSERT
and MERGE
statements (not shown here) must use the partition extended syntax to identify the partition to which the row should be added.
While delete and update operations do not require the partition extended syntax。
C.必须为每个分区指定同样的物理属性。错误。Each partition can have different physical attributes。
D.唯一本地索引不能在系统分区表上被创建。
E.传统的分区修剪和智能化分区连接不支持这些表。