Using System Partitioning

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 Reference

ADDCI4481

Advantages 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.

ADDCI4482

Implementing System Partitioning

This section describes how to implement system partitioning.

ADDCI4483

Creating 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
);

ADDCI4485

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.

ADDCI4488

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.

ADDCI4489

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 (as SELECT)

• 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

ADDCI4491

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.

1. 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.

2. The system invokes the ODCIStatsUpdPartStatistics() on the affected partitions.

3. The system drops the partition that has been split from all system-partition index and statistics storage tables.

4. The system adds two new partitions to the system-partitioned tables.

5. 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.

ADDCI4492

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:

1. ODCIIndexExchangePartition() for the affected partition and index

2. 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.传统的分区修剪和智能化分区连接不支持这些表。