WITH VALIDATION 与WITHOUT VALIDATION对分区交换的影响

最近将一个比较大的历史表改造为分区表，中间用到了分区交换，为了搞清楚分区交换时WITHOUT VALIDATION与WITH VALIDATION以及是否禁用主键约束的区别，
用10046对各种情况进行了跟踪对比：

1.禁用主键约束+without validation
ALTER TABLE BC_LOG_TRANSFER_EB_HIS1 DISABLE CONSTRAINT PK_LOG_TRANSFER_EB_HIS KEEP INDEX;

alter session set events '10046 trace name context forever,level 12';
ALTER TABLE BC_LOG_TRANSFER_EB_HIS1 EXCHANGE PARTITION P201304 WITH TABLE BC_LOG_TRANSFER_EB_HIS0 INCLUDING INDEXES WITHOUT VALIDATION;
alter session set events '10046 trace name context off';

ALTER TABLE BC_LOG_TRANSFER_EB_HIS1 ENABLE CONSTRAINT PK_LOG_TRANSFER_EB_HIS;
***********************************************************************************************
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       59      0.02       0.02          0          0          0           0
Execute     76      0.06       0.12          5         59         39          16
Fetch       69      0.02       0.26         26        138          0          69
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      204      0.11       0.41         31        197         39          85

Misses in library cache during parse: 9
Misses in library cache during execute: 6

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        6        0.00          0.00
  db file sequential read                        31        0.03          0.30

2.不禁用主键约束+without validation
alter session set events '10046 trace name context forever,level 12';
ALTER TABLE BC_LOG_TRANSFER_EB_HIS1 EXCHANGE PARTITION P201304 WITH TABLE BC_LOG_TRANSFER_EB_HIS0 INCLUDING INDEXES WITHOUT VALIDATION;
alter session set events '10046 trace name context off';
******************************************************************************
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       56      0.02       0.02          0          0          0           0
Execute     67      0.04       0.16          6         59         39          16
Fetch       47      2.28       2.92       2408      26711          1          31
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      170      2.36       3.11       2414      26770         40          47

Misses in library cache during parse: 4

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        7        0.04          0.04
  db file scattered read                          1        0.01          0.01
  db file sequential read                      2407        0.07          0.98

select /*+ first_rows(1) ordered */ 1
from
 "SYS"."BC_LOG_TRANSFER_EB_HIS0" A ,"SYS"."BC_LOG_TRANSFER_EB_HIS1" B where
  "A"."CREDIT_ID" = "B"."CREDIT_ID" and "A"."TRAN_DATE" = "B"."TRAN_DATE" and
  "A"."CHANNEL" = "B"."CHANNEL" and (
  tbl$or$idx$part$num("SYS"."BC_LOG_TRANSFER_EB_HIS1",0,0,0 ,B .rowid )  <  4
  or  tbl$or$idx$part$num("SYS"."BC_LOG_TRANSFER_EB_HIS1",0,0,0 ,B .rowid )
  >  4 ) and tbl$or$idx$part$num("SYS"."BC_LOG_TRANSFER_EB_HIS1",0,0,0 ,A
  .TRAN_DATE )  <>  4 and rownum < 2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      2.27       2.84       2399      26652          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.28       2.85       2399      26652          0           0

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  COUNT STOPKEY (cr=26652 pr=2399 pw=0 time=2842034 us)
         0          0          0   NESTED LOOPS  (cr=26652 pr=2399 pw=0 time=2842009 us cost=0 size=128 card=2)
         0          0          0    INDEX FULL SCAN PK_LOG_TRANSFER_EB_HIS0 (cr=26652 pr=2399 pw=0 time=2842003 us cost=0 size=78 card=3)(object id 80228)
         0          0          0    PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=0 size=38 card=1)
         0          0          0     INDEX UNIQUE SCAN PK_LOG_TRANSFER_EB_HIS PARTITION: KEY KEY (cr=0 pr=0 pw=0 time=0 us cost=0 size=38 card=1)(object id 80177)

3.不禁用主键约束+with validation
alter session set events '10046 trace name context forever,level 12';
ALTER TABLE BC_LOG_TRANSFER_EB_HIS1 EXCHANGE PARTITION P201304 WITH TABLE BC_LOG_TRANSFER_EB_HIS0 INCLUDING INDEXES WITH VALIDATION;
alter session set events '10046 trace name context off';

************************************************************************************
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       56      0.01       0.02          0          0          0           0
Execute     83      0.08       0.15          5         59         39          16
Fetch       63      1.76      24.95      12224      13159          1         207
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      202      1.87      25.12      12229      13218         40         223

Misses in library cache during parse: 9
Misses in library cache during execute: 7

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        7        1.27          1.27
  db file sequential read                        11        1.88          5.88
  db file scattered read                        113        2.12         15.05

select 1
from
 "BC_LOG_TRANSFER_EB_HIS0" where
  TBL$OR$IDX$PART$NUM("BC_LOG_TRANSFER_EB_HIS1", 0, 3,1048576,"TRAN_DATE") !=
  :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      1.74      17.38      12218      13055          1           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      1.75      17.38      12218      13055          1           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  TABLE ACCESS FULL BC_LOG_TRANSFER_EB_HIS0 (cr=13055 pr=12218 pw=0 time=17383845 us)

结论：
1.当禁用主键约束，不进行validation时，分区交换仅是数据字典级的操作，不会对表或索引进行扫描
2.当不禁用主键约束，进行validation时，分区交换需要对表进行全表扫描，来做validation。
3.当不禁用主键约束，不进行validation时，分区交换需要对主键索引进行扫描来完成主键约束的校验。

因此，只有在禁用主键约束并不进行validation时，分区交换才是效率最快的。
  

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

转载于:http://blog.itpub.net/10972173/viewspace-1069992/