Insert Menggunakan Metode Exchange Partition

Berikut ini contoh sederhana penggunaan metode exchange partition :

Create table destination
------------------------------
CREATE TABLE call_BEBEK (
    id    NUMBER(12,6),
    v1    VARCHAR2(10),
    data  VARCHAR2(100)
)
PARTITION BY RANGE(id) (
    PARTITION P_1 VALUES LESS THAN (2)
) TABLESPACE users;


set serveroutput on;
set heading off;
set feedback off;
set linesize 800;
set pagesize 999;
declare
    v_no number:=1;
begin
DBMS_OUTPUT.ENABLE(1000000);
for i in 1..31 loop
dbms_output.put_line('ALTER TABLE  call_bebek   ADD PARTITION  P_'||to_char(v_no+i) ||' VALUES LESS THAN ('||TO_CHAR (v_no+1+i)||')  TABLESPACE  USERS;');
dbms_output.put_line(' ');
 
end loop;
end;

ALTER TABLE  call_bebek   ADD PARTITION  P_2 VALUES LESS THAN (3)  TABLESPACE  USERS;


ALTER TABLE  call_bebek   ADD PARTITION  P_3 VALUES LESS THAN (4)  TABLESPACE  USERS;


ALTER TABLE  call_bebek   ADD PARTITION  P_4 VALUES LESS THAN (5)  TABLESPACE  USERS;


ALTER TABLE  call_bebek   ADD PARTITION  P_5 VALUES LESS THAN (6)  TABLESPACE  USERS;


ALTER TABLE  call_bebek   ADD PARTITION  P_6 VALUES LESS THAN (7)  TABLESPACE  USERS;


ALTER TABLE  call_bebek   ADD PARTITION  P_7 VALUES LESS THAN (8)  TABLESPACE  USERS;


ALTER TABLE  call_bebek   ADD PARTITION  P_8 VALUES LESS THAN (9)  TABLESPACE  USERS;


ALTER TABLE  call_bebek   ADD PARTITION  P_9 VALUES LESS THAN (10)  TABLESPACE  USERS;




Create local Index
---------------------------
CREATE index bebek_id on call_bebek(id) local tablespace users;


create table source
--------------------------
CREATE TABLE call_temp_BEBEK (
    id    NUMBER(12,6),
    v1    VARCHAR2(10),
    data  VARCHAR2(100)
)TABLESPACE USERS;



Insert Data
----------------------------
INSERT /*+ append ordered full(s1) use_nl(s2) */
INTO call_temp_bebek
SELECT
        TRUNC((ROWNUM-1)/500,6),
        TO_CHAR(ROWNUM),
        RPAD('X',100,'X')
FROM
        all_tables s1,
        all_tables s2
WHERE
        ROWNUM <= 10000;





Update record to syncronize with table partition
-----------------------------------------------------
Update call_temp_bebek
set ID=8


Insert using exchange command  (Metode ini berjalan dua arah dari partition ke non partition atau sebaliknya dengan perintah yang sama dibawah ini)
------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE CALL_BEBEK
  EXCHANGE PARTITION P_8 WITH TABLE call_temp_bebek;


ALTER TABLE sales
   EXCHANGE PARTITION feb97 WITH TABLE sales_feb97
   WITHOUT VALIDATION;