玉面飞龙的BLOG

当exchange partition的时候,如果参与的表上存在主键或者唯一约束的话,Oracle将忽略without validation子句进行数据验证。如果数据量较大，数据验证将花费很多时间。

如下例,当表large_log_xxx上存在主键约束的时候,进行数据验证的SQL在使用很恐怖的执行计划。

SQL> alter table LARGE_LOG_XXX exchange partition part2 with table
TMP_LARGE_LOG_XXX INCLUDING INDEXES without validation;

SQL_TEXT
—————————————————————-
select /*+ first_rows(1) ordered */ 1 from  “TMP_LARGE_LOG_XXX” “A” ,”LARGE_LOG_XXX” “B” where
“A”.”ID” = “B”.”ID” and “A”.”PARTITION_KEY” = “B”.”PARTITION_KEY” and
( tbl$or$idx$part$num(”LARGE_LOG_XXX”,0,1,0 ,”B” .”PARTITION_KEY” )  <  3 or
tbl$or$idx$part$num(”LARGE_LOG_XXX”,0,1,0 ,”B” .”PARTITION_KEY” )  >  3 )
and tbl$or$idx$part$num(”LARGE_LOG_XXX”,0,1,0 ,”A” .”PARTITION_KEY” )  <>  3
and rownum < 2

————————————————————————–
| Id  | Operation                  |  Name                     | Rows  | Bytes | Cost  | Pstart| Pstop |
————————————————————————–
|   0 | SELECT STATEMENT           |                           |       |       |    16M|       |       |
|   1 |  COUNT STOPKEY             |                           |       |       |       |       |       |
|   2 |   NESTED LOOPS             |                           |  1561K|    52M|    16M|       |       |
|   3 |    PARTITION HASH ALL      |                           |       |       |       |     1 |    16 |
|   4 |     INDEX FULL SCAN        | TMP_LARGE_LOG_XXX_PK      |    16M|   397M|    26 |     1 |    16 |
|   5 |    PARTITION RANGE ITERATOR|                           |       |       |       |   KEY |   KEY |
|   6 |     PARTITION HASH ITERATOR|                           |       |       |       |   KEY |   KEY |
|*  7 |      INDEX UNIQUE SCAN     | LARGE_LOG_XXX             |     1 |     9 |     1 |       |       |
————————————————————————-
Predicate Information (identified by operation id):
—————————————————
7 - access(”ID”=”ID” AND “PARTITION_KEY”=”PARTITION_KEY”)

为了避免不必要的数据验证，可以事先disable PK或者Unique Constraints,则指定without validation后，oracle将不会验证数据完整性。

alter table LARGE_LOG_XXX DISABLE CONSTRAINT constraint_name KEEP INDEX;

oracle中文档的说明,

Note:

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation will be performed as if WITH VALIDATION were specified. This is in order to maintain the integrity of the constraints.

To avoid the overhead of this validation activity, issue the following statement for each constraint before doing the exchange partition operation:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name KEEP INDEX

Then, enable the constraints after the exchange.