Partition Exchange

Ein kleines Syntaxbeispiel zum Partition Exchange und Partition Splitting, das ich als vereinfachte Variante eines selbst bereits relativ einfachen Beispiels aus Tim Halls Sammlungen erstellt habe...

Zunächst das Test-Script:

-- test_part_exchange.sql
-- als minimales Syntax-Beispiel für Partition Exchange und Partition Splitting

column high_value format a100

drop table test_part_exchange_source;
drop table test_part_exchange_dest;

create table test_part_exchange_source
as
select rownum id
     , trunc(sysdate) - 100 + mod(rownum, 100) created_date
  from dual
connect by level <= 1000000;

select trunc(created_date, 'mm') create_month
     , count(*) cnt
  from test_part_exchange_source
 group by trunc(created_date, 'mm')
 order by trunc(created_date, 'mm');

create table test_part_exchange_dest
( id number
, created_date date)
partition by range (created_date)
(partition test_part_exchange_dest_2011 values less than (to_date('01.01.2012', 'dd.mm.yyyy') ) );

alter table test_part_exchange_dest
exchange partition test_part_exchange_dest_2011
with table test_part_exchange_source;

exec dbms_stats.gather_table_stats(user, 'TEST_PART_EXCHANGE_DEST')

select partition_name
	 , num_rows
     , high_value
  from user_tab_partitions
 where table_name = 'TEST_PART_EXCHANGE_DEST';

-- @ trace 
 
alter table test_part_exchange_dest
split partition test_part_exchange_dest_2011 at (to_date('30.06.2011 23:59:00', 'dd.mm.yyyy hh24:mi:ss'))
into (partition test_part_exchange_dest_201106,
      partition test_part_exchange_dest_201112);

-- @ trace_end
	  
exec dbms_stats.gather_table_stats(user, 'TEST_PART_EXCHANGE_DEST')

select partition_name
	 , num_rows
     , high_value
  from user_tab_partitions
 where table_name = 'TEST_PART_EXCHANGE_DEST';

Das Ergebnis dazu in meiner 11.1.0.7-Datenbank ist dann erwartungsgemäß:

SQL> @ test_part_exchange

Tabelle wurde gelöscht.

Tabelle wurde gelöscht.

Tabelle wurde erstellt.

CREATE_MON        CNT
---------- ----------
01.05.2011     300000
01.06.2011     300000
01.07.2011     310000
01.08.2011      90000

Tabelle wurde erstellt.

Tabelle wurde geändert.

PL/SQL-Prozedur erfolgreich abgeschlossen.

PARTITION_NAME                   NUM_ROWS HIGH_VALUE
------------------------------ ---------- -----------------------------------------------------------------------------------
TEST_PART_EXCHANGE_DEST_2011      1000000 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Tabelle wurde geändert.

PL/SQL-Prozedur erfolgreich abgeschlossen.

PARTITION_NAME                   NUM_ROWS HIGH_VALUE
------------------------------ ---------- -----------------------------------------------------------------------------------
TEST_PART_EXCHANGE_DEST_201106     600000 TO_DATE(' 2011-06-30 23:59:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PART_EXCHANGE_DEST_201112     400000 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Alles also recht übersichtlich. Wenn man einen Blick ins 10046er Trace wirft, sieht man dort neben den zu erwartenden Operationen am Data Dictionary auch noch ein LOCK TABLE ... PARTITION ... IN EXCLUSIVE MODE NOWAIT, was auch keine große Überraschung ist, aber andeutet, dass man beim Splitting großer Partitionen Vorsicht walten lassen sollte.

LOCK TABLE "TEST_PART_EXCHANGE_DEST" PARTITION 
  ("TEST_PART_EXCHANGE_DEST_2011")  IN EXCLUSIVE MODE  NOWAIT 

...

********************************************************************************

alter table test_part_exchange_dest
split partition test_part_exchange_dest_2011 at (to_date('30.06.2011 23:59:00', 'dd.mm.yyyy hh24:mi:ss'))
into (partition test_part_exchange_dest_201106,
      partition test_part_exchange_dest_201112)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.79       0.94          1       1354       2692     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.79       0.94          1       1354       2692     1000000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 68  

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  LOAD AS SELECT  (cr=1735 pr=0 pw=1232 time=0 us)
1000000   PARTITION RANGE SINGLE PARTITION: 1 1 (cr=1237 pr=0 pw=0 time=0 us cost=480 size=12000000 card=1000000)
1000000    TABLE ACCESS FULL TEST_PART_EXCHANGE_DEST PARTITION: 1 1 (cr=1237 pr=0 pw=0 time=0 us cost=480 size=12000000 card=1000000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  control file sequential read                    5        0.00          0.00
  direct path write                              99        0.01          0.11
  direct path sync                                2        0.05          0.06
  enq: RO - fast object reuse                     1        0.01          0.01
  db file sequential read                         1        0.00          0.00
  rdbms ipc reply                                 1        0.00          0.00
  log file sync                                   1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

Die eigentliche Arbeit der Reorganisation scheint also über direct path zu erfolgen, was auch wieder ein gutes Argument für das exklusive LOCK ist.