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

228.Evaluate the following statements:

CREATE TABLE purchase_orders (

po_id NUMBER(4),

po_date TIMESTAMP, 

supplier_id NUMBER(6), 

po_total NUMBER(8,2),

CONSTRAINT order_pk PRIMARY KEY(po_id)) 

PARTITION BY RANGE(po_date)

(PARTITION Q1 VALUES LESS THAN (TO_DATE(?1-apr-2007?d-mon-yyyy?),

 PARTITION Q2 VALUES LESS THAN (TO_DATE(?1-jul-2007?d-mon-yyyy?),

 PARTITION Q3 VALUES LESS THAN (TO_DATE(?1-oct - 2007?d-mon-yyyy?), 

PARTITION Q4 VALUES LESS THAN  (TO_DATE(?1-jan-2008?d-mon-yyyy?));

CREATE TABLE purchase_order_items (

po_id NUMBER(4) NOT NULL, 

product_id NUMBER(6) NOT NULL, 

unit_price NUMBER(8,2), 

quantity NUMBER(8), 

CONSTRAINT po_items_fk FOREIGN KEY (po_id) REFERENCES purchase_orders(po_id)) PARTITION BY REFERENCE(po_items_fk);

What are the two consequences of the above statements? (Choose two.)

A. Partitions of PURCHASE_ORDER_ITEMS have system-generated names.

B. Both PURCHASE_ORDERS and PURCHASE_ORDER_ITEMS tables are created with four partitions each.

C. Partitions of the PURCHASE_ORDER_ITEMS table exist in the same tablespaces as the partitions of the PURCHASE_ORDERS table.

D. The PURCHASE_ORDER_ITEMS table inherits the partitioning key from the parent table by automatically duplicating the key columns.

E. Partition maintenance operations performed on the PURCHASE_ORDER_ITEMS table are automatically reflected in the PURCHASE_ORDERS table.

Answer: BC

答案解析：

参考：http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#i1006455

Creating Reference-Partitioned Tables

To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table. The referential constraint must be enabled and enforced.

As with other partitioned tables, you can specify object-level default attributes, and you can optionally specify partition descriptors that override the object-level defaults on a per-partition basis.

Example 4-9 creates a parent table orders which is range-partitioned on order_date. The reference-partitioned child table order_items is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.

Example 4-9 Creating reference-partitioned tables

CREATE TABLE orders

    ( order_id           NUMBER(12),

      order_date         TIMESTAMP WITH LOCAL TIME ZONE,

      order_mode         VARCHAR2(8),

      customer_id        NUMBER(6),

      order_status       NUMBER(2),

      order_total        NUMBER(8,2),

      sales_rep_id       NUMBER(6),

      promotion_id       NUMBER(6),

      CONSTRAINT orders_pk PRIMARY KEY(order_id)

    )

  PARTITION BY RANGE(order_date)

    ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),

      PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),

      PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),

      PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))

    );

 

CREATE TABLE order_items

    ( order_id           NUMBER(12) NOT NULL,

      line_item_id       NUMBER(3)  NOT NULL,

      product_id         NUMBER(6)  NOT NULL,

      unit_price         NUMBER(8,2),

      quantity           NUMBER(8),

      CONSTRAINT order_items_fk

      FOREIGN KEY(order_id) REFERENCES orders(order_id)

    )

    PARTITION BY REFERENCE(order_items_fk);

If partition descriptors are provided, then the number of partitions described must exactly equal the number of partitions or subpartitions in the referenced table. If the parent table is a composite partitioned table, then the table has one partition for each subpartition of its parent; otherwise the table has one partition for each partition of its parent.

Partition bounds cannot be specified for the partitions of a reference-partitioned table.

The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it inherits its name from the corresponding partition in the parent table, unless this inherited name conflicts with an existing explicit name. In this case, the partition has a system-generated name.

Partitions of a reference-partitioned table collocate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition.