OCP考试第17题

17. The INV_HISTORY table is created using the command:

SQL>CREATE TABLE INV_HISTORY (inv_no NUMBER(3), inv_date DATE, inv_amt NUMBER(10,2))

partition by range (inv_date)

interval (numtoyminterval(1,'month'))

(partition p0 values less than (to_date('01-01-2005','dd-mm-yyyy')),

partition p1 values less than (to_date('01-01-2006','dd-mm-yyyy')));

The following data has been inserted into the INV_HISTORY table :

INV_NO INV_DATE INV_AMT 1 30-dec-2004 1000 2 30-dec-2005 2000 3 1-feb-2006 3000 4 1-mar-2006

4000 5 1-apr-2006 5000

You would like to store the data belonging to the year 2006 in a single partition and issue the command:

SQL> ALTER TABLE inv_history MERGE PARTITIONS

FOR(TO_DATE('15-feb-2006','dd-mon-yyyy')), FOR(TO_DATE('15-apr-2006')) INTO PARTITION sys_py;

What would be the outcome of this command?

融合分区

A. It executes successfully, and the transition point is set to '1-apr-2006'.

B. It executes successfully, and the transition point is set to '15-apr-2006'.

C. It produces an error because the partitions specified for merging are not adjacent.

D. It produces an error because the date values specified in the merge do not match the date values

 

知识点：

融合（merge partition）分区：你可以融合范围分区表和list分区表的两个分区到一个分区，两个原始的分区会被删除，这个时候ROWID也就改变了，当然也包括删除相关的本地索引。
限制：1、不能为hash分区使用这个语法，因为hash分区已经有了Coalescing Partitions分区的语法。
      2、当融合范围分区的时候必须是相邻的分区,否则报错ORA-14274: 要合并的分区不相邻，但是list分区并没有这个限制。
      如果融合的时候
      3、如果合并范围分区表的分区和maxvalue分区，那合并后的还是maxvalue分区。
      4、如果何必list分区的分区和default分区，那合并后的还是default分区。
      5、如果合并的分区中有数据,那全局索引和普通索引均失效，除非带上update indexes
      6、如果合并的分区中有数据，那本地索引关于合并后新分区的索引会失效，除非带上update indexes

参考 http://blog.itpub.net/7728585/viewspace-757019/

 

答案：C，分区不连续。