由官方Doc中一个Note引发的思考

Oracle在官方文档介绍 Data Pump的Using Data File Copying to Move Data一节提到了以下Note内容：

Note: Data Pump does not load tables with disabled unique indexes. To load data into the table, the indexes must be either dropped or reenabled.

问题一：为什么不允许将数据load到带有disabled unique index的表中？
解决方案：测试一下向上述表中插入数据，会有什么现象？
测试过程：
    1. 在表中创建一个unique index
SQL> DESC SCOTT.BONUS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 SAL                                                NUMBER
 COMM                                               NUMBER


SQL> CREATE UNIQUE INDEX BONUS_UNIQUE_INDEX ON SCOTT.BONUS(ENAME);
Index created.

    2. disable掉改unique index
SQL> alter index bonus_unique_index disable;  
alter index bonus_unique_index disable
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option                                                                

报错，引发问题二

问题二：为什么不允许使用上述语法alter index bonus_unique_index disable;
原因：
引用官方文档介绍：
ENABLE Clause
ENABLE applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if these conditions are true:

• The function is currently valid
• The signature of the current function matches the signature of the function when the index was created
• The function is currently marked as DETERMINISTIC

Restriction on Enabling Function-based Indexes You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.
DISABLE Clause
DISABLE applies only to a function-based index. This clause lets you disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.
UNUSABLE Clause
Specify UNUSABLE to mark the index or index partition(s) or index subpartition(s) UNUSABLE. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked UNUSABLE, the other partitions of the index are still valid. You can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.
Restriction on Marking Indexes Unusable You cannot specify this clause for an index on a temporary table.

只有function-based index才可以使用DISABLE子句，unique index只能使用UNUSABLE子句，虽然两个子句都会直接将index drop掉。引发问题三。

问题三：为什么在设计数据库时对function-based index可以选择DISABLE和ENABLE，而其他index只能选择UNUSABLE和REBUILD(或者drop后recreate)？
原因：未找到。

回到问题二，解决方案：
SQL> ALTER INDEX SYS.BONUS_UNIQUE_INDEX UNUSABLE;
Index altered.

再继续回到问题一：
我们尝试向BONUS表中插入数据

SQL> insert into scott.bonus values('Yang','DBA',342, 12);
insert into scott.bonus values('Yang','DBA',342, 12)
*
ERROR at line 1:
ORA-01502: index 'SYS.BONUS_UNIQUE_INDEX' or partition of such index is in unusable state

插入失败，因此，当尝试向带有disable的unique index的表中导入数据时，也会因为唯一性索引目前为UNUSABLE状态而失败。只有当删除或者重建该唯一性索引后，才可能导入数据。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30349018/viewspace-2134948/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30349018/viewspace-2134948/