原由：
今天在rename表时,特别是表中包含主键索引和唯一索引时也需要将constraint进行rename；
否则在重建回原始表的主键名称时将报错；
类似如下：
*
ERROR at line 1:
ORA-02264: name already used by an existing constraint
后面特地的留意下unique索引情况，一个有兴趣事情发生；
SQL> create table test6 as select object_id,object_name from dba_objects;
Table created.
SQL> create table test6u nologging as select * from test6;
Table created.
SQL> create unique index uni_test6 on test6(object_id);
Index created.
SQL> alter table test6u add constraint uni_test7 unique (object_id);
Table altered.
---在表中dba_constraint查询约束内容
select constraint_name,table_name,constraint_type from dba_constraints
where owner='AIKI2'
and table_name in ('TEST6','TEST6U')
/
CONSTRAINT_NAME TABLE_NAME C
------------------------------ ------------------------------ -
UNI_TEST6U TEST6U U
发现只有test6u包含，而test6没有内容；
接着我们检查执行计划是否有变化
SQL> exec dbms_stats.gather_table_stats(user,'TEST6',cascade=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'TEST6U',cascade=>true);
PL/SQL procedure successfully completed.
SQL> set autotrace ON EXPLAIN STATISTICS
SQL> set linesize 150
SQL> l
1* select * from test6 where object_id=7979
SQL> /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
7979 ODCIINDEXINFOFLAGSDUMP
Execution Plan
----------------------------------------------------------
Plan hash value: 2651143101
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST6 | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | UNI_TEST6 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=7979)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
492 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> l
1* select * from test6u where object_id=7979
SQL> /
SQL> /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
7979 ODCIINDEXINFOFLAGSDUMP
Execution Plan
----------------------------------------------------------
Plan hash value: 3556035271
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST6U | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | UNI_TEST6U | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=7979)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
492 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到执行计划并未有什么不同，那为什么会有这样的差异呢在dba_constraints,有什么用呢？
我们找到administrator文档里有这么一段话
Efficient Use of Integrity Constraints: A Procedure
Using integrity constraint states in the following order can ensure the best benefits:
1. Disable state.
2. Perform the operation (load, export, import).
3. Enable novalidate state.
4. Enable state.
Some benefits of using constraints in this order are:
■ No locks are held.
■ All constraints can go to enable state concurrently.
■ Constraint enabling is done in parallel.
■ Concurrent activity on table is permitted.
大意就是指好处有：
不会持有锁，可以开并行操作
1* select /*+ parallel(t,2) */* from test6u t where object_id=2878
SQL> /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
2878 V_$CPOOL_CC_STATS
Execution Plan
----------------------------------------------------------
Plan hash value: 3556035271
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST6U | 1 | 29 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | UNI_TEST6U | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
以上我们看到虽然采用了并行的hint但执行计划并未显示并行操作；
alter table test6u disable constraint uni_test6u;
SQL> l
1* alter table test6u disable constraint uni_test6u
SQL> select /*+ parallel(t,2) */* from test6u t where object_id=2878
2 /
OBJECT_ID OBJECT_NAME
---------- ------------------------------
2878 V_$CPOOL_CC_STATS
Execution Plan
----------------------------------------------------------
Plan hash value: 2220473316
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 29 | 55 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1 | 29 | 55 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1 | 29 | 55 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TEST6U | 1 | 29 | 55 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
我们可以看到并行计划已经出现了；
