OCP-1Z0-053-V13.02-245题

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

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.

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.

  
  

  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.

  Example 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 inExample 8-16. DATAOBJ_TO_PARTITION can also be used, as demonstrated by Example 8-18.

  Example 8-17 Inserting Data into a System-Partitioned Table

  INSERT INTO SystemPartitionedTable PARTITION (p1) VALUES (4,5);

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

    • Creation of local indexes, with the exception of unique local indexes because they require a partitioning key D正确

    • 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:

      Example 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