OCP考試題庫-IZ0-052 第10題

 Examine the following statement that is used to modify the primary key constraint on the SALES
table:
SQL> ALTER TABLE SALES MODIFY CONSTRAINT pk DISABLE VALIDATE;
Which three statements are true regarding the above command? (Choose three.)

A.The constraint remains valid.
B.The index on the constraint is dropped.
C.It allows the loading of data into the table using SQL *Loader.
D.New data conforms to the constraint, but existing data is not checked.
E.It allows the data manipulation on the table using INSERT/UPDATE/DELETE SQL statements.

正確答案： ABC

DISABLE VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, because it lets you load large amounts of data while also saving space by not having an index. This setting lets you load data from a nonpartitioned table into a partitioned table using the exchange_partition_subpart clause of the ALTER TABLE statement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed

大意是說DISABLE VALIDATE會disable鍵並刪除在 constraint上的索引，同時， constraint還是valid。這個特性在資料倉儲的環境中特別有用。因為當載入大量資料時，沒有索引，可以節省很大的空間（也省去了維護索引的資源）。
這個設定允許使用從非分割槽表中通過分割槽交換向分割槽表中載入資料。其他所有DML雲集對錶的修改都不允許。

SQL> create table test2 as select * from test1;
Table created.
SQL> alter table test2 add constraint pk_test2 primary key (pid);
Table altered.
SQL> alter table test2 modify constraint pk_test2 disable validate;
Table altered.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2040194/，如需轉載，請註明出處，否則將追究法律責任。