Partitioning and Storage-Related Enhancements
BackBy — February 14, 2008
These questions are based on 1Z0-050 – Oracle Database 11g New Features
Oracle Self Test Software Practice Test.
Objective: Partitioning and storage-related enhancements.
Sub-objective: Implement the new partitioning methods.
Multiple answer, multiple choice
You execute the CREATE TABLE statement shown in the exhibit to create the EMPLOYEES table. (Click the Exhibit(s) button.)
Which two statements are true? (Choose two.)
- The EMPLOYEES table is created with four partitions.
- New partitions are added to the EMPLOYEES table as needed when data is inserted.
- Partition pruning can occur for the specified partitioning key.
- The table is not created because it uses a virtual column for the partitioning key.
A. The EMPLOYEES table is created with four partitions.
C. Partition pruning can occur for the specified partitioning key.
The EMPLOYEES table is created with four partitions, and partition pruning can occur for the specified partitioning key.
You can create a partition table using a virtual column as the partitioning key. In this scenario, you specify the virtual column BONUS_AMT as the partitioning key. The statement executes and creates the table with the four specified partitions. The values in the BONUS_AMT column are evaluated as needed and not physically stored in the table. Partition pruning and partition-wise joins are supported for tables that are partitioned on virtual columns.
The option that states new partitions are added to the EMPLOYEES table as needed when data is inserted is incorrect. This is true for interval partitioning, but in this scenario, you specified only a range partition. You would have to add any new partitions before inserting data into them or an ORA-14400: inserted partition key does not map to any partition error would be generated.
The option that states the table is not created because it uses a virtual column for the partitioning key is incorrect. You can use a virtual column for the partitioning key.
Oracle University - Oracle Database 11g: New Features For Administrators - Chapter 9: Partitioning and Storage-Related Enhancements - Virtual Column-Based Partitioning
Oracle Database SQL Language Reference 11g Release 1 (11.1)
Part Number B28286-02
Oracle Database VLDB and Partitioning Guide 11g Release 1 (11.1)
Part Number B32024-01