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)

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