Wednesday, June 13, 2012
Part 41 of series - Re-define tables and create referecne partitions on child tables
With your tongue, chant the Name of the One Lord. In this world, it shall bring you peace, comfort and great joy; hereafter, it shall go with your soul, and shall be of use to you. Pause .The disease of your ego shall be eradicated. By Guru’s Grace, practice Raja Yoga, the Yoga of meditation and success. Those who taste the sublime essence of the Lord have their thirst quenched. Those who have found the Lord, the Treasure of peace, shall not go anywhere else again. Those, unto whom the Guru has given the Lord's Name, Har, Har — O Nanak, their fears are removed.
Index to all the Posts of Gurpartap Singh's Blog
Part 1:
Lets create 3 tables one parent and two as its childern and they are not partitioned:
-- Parent
CREATE TABLE GSINGH.TRANSACTION (
TRANSACTION_ID NUMBER PRIMARY KEY,
TRANSACTION_DATE DATE NOT NULL,
ACCOUNT_ID NUMBER NOT NULL,
DEVICE_ID NUMBER)
;
CREATE INDEX GSINGH.TRANSACTION_date_ind ON TRANSACTION(ACCOUNT_ID);
--============================================================
-- Child 1
--Create child table ORDERS with reference partitioning:
CREATE TABLE GSINGH.ORDERS (
TRANSACTION_ID NUMBER NOT NULL,
ORDER_ID NUMBER PRIMARY KEY,
PRICE NUMBER,
QUANTITY NUMBER,
CONSTRAINT orders_fk FOREIGN KEY (TRANSACTION_ID) REFERENCES TRANSACTION
);
--============================================================
-- Child 2
--Create child table LEDGER with reference partitioning:
CREATE TABLE GSINGH.LEDGER (
LEDGER_ID NUMBER PRIMARY KEY,
TRANSACTION_ID NUMBER NOT NULL,
CURRENCY_CODE varchar(3),
PAYMENT_TOTAL NUMBER,
BALANCE NUMBER,
CONSTRAINT LEDGER_fk FOREIGN KEY (TRANSACTION_ID) REFERENCES TRANSACTION
);
You can see that they are not partitioned and have been created using:
set feed on
select count(*) from user_tab_partitions;
select * from transaction;
select * from orders;
select * from ledger;
Now lets insert data in them:
--============================================================
--Insert data to Transaction Table
INSERT INTO GSINGH.TRANSACTION VALUES (1,'01-JAN-05',10020,11);
INSERT INTO GSINGH.TRANSACTION VALUES (2,'01-JUL-05',10040,14);
INSERT INTO GSINGH.TRANSACTION VALUES (3,'01-JAN-06',10238,17);
INSERT INTO GSINGH.TRANSACTION VALUES (4,'01-JUL-06',10765,16);
INSERT INTO GSINGH.TRANSACTION VALUES (5,'01-JAN-07',10111,18);
INSERT INTO GSINGH.TRANSACTION VALUES (6,'01-JUL-07',10757,30);
INSERT INTO GSINGH.TRANSACTION VALUES (7,'01-JAN-08',10768,15);
INSERT INTO GSINGH.TRANSACTION VALUES (8,'01-JUL-08',10986,3);
INSERT INTO GSINGH.TRANSACTION VALUES (9,'01-JAN-09',19879,5);
INSERT INTO GSINGH.TRANSACTION VALUES (10,'01-JUL-09',15671,7);
INSERT INTO GSINGH.TRANSACTION VALUES (11,'01-JAN-10',16873,23);
INSERT INTO GSINGH.TRANSACTION VALUES (12,'01-JUL-10',98746,26);
INSERT INTO GSINGH.TRANSACTION VALUES (13,'01-JAN-11',24571,29);
INSERT INTO GSINGH.TRANSACTION VALUES (14,'01-JUL-11',87645,21);
INSERT INTO GSINGH.TRANSACTION VALUES (15,'01-JAN-12',45872,19);
INSERT INTO GSINGH.TRANSACTION VALUES (16,'01-JUL-12',34587,10);
INSERT INTO GSINGH.TRANSACTION VALUES (17,'01-JAN-13',12354,26);
INSERT INTO GSINGH.TRANSACTION VALUES (18,'01-JUL-13',87659,1);
commit;
--Insert data to ORDERS Table
INSERT INTO GSINGH.ORDERS VALUES (1,100,4.99,1);
INSERT INTO GSINGH.ORDERS VALUES (2,101,7.99,1);
INSERT INTO GSINGH.ORDERS VALUES (3,102,14.99,1);
INSERT INTO GSINGH.ORDERS VALUES (4,103,5.99,3);
INSERT INTO GSINGH.ORDERS VALUES (5,104,7.99,2);
INSERT INTO GSINGH.ORDERS VALUES (6,105,9.99,1);
INSERT INTO GSINGH.ORDERS VALUES (7,106,12.99,2);
INSERT INTO GSINGH.ORDERS VALUES (8,107,21.99,5);
INSERT INTO GSINGH.ORDERS VALUES (9,108,31.99,6);
INSERT INTO GSINGH.ORDERS VALUES (10,109,53.99,1);
INSERT INTO GSINGH.ORDERS VALUES (11,110,34.99,2);
INSERT INTO GSINGH.ORDERS VALUES (12,111,54.99,3);
INSERT INTO GSINGH.ORDERS VALUES (13,112,67.99,5);
INSERT INTO GSINGH.ORDERS VALUES (14,113,2.99,1);
INSERT INTO GSINGH.ORDERS VALUES (15,114,1.99,1);
INSERT INTO GSINGH.ORDERS VALUES (16,115,8.99,2);
INSERT INTO GSINGH.ORDERS VALUES (17,116,6.99,1);
INSERT INTO GSINGH.ORDERS VALUES (18,117,4.99,5);
commit;
--Insert data to LEDGER Table
INSERT INTO GSINGH.LEDGER VALUES (34561,1,'USA',7.00,3.99);
INSERT INTO GSINGH.LEDGER VALUES (34562,2,'USA',17.00,13.99);
INSERT INTO GSINGH.LEDGER VALUES (34563,3,'USA',70.00,43.99);
INSERT INTO GSINGH.LEDGER VALUES (34564,4,'USA',15.00,13.99);
INSERT INTO GSINGH.LEDGER VALUES (34565,5,'USA',16.00,12.99);
INSERT INTO GSINGH.LEDGER VALUES (34566,6,'USA',47.00,43.99);
INSERT INTO GSINGH.LEDGER VALUES (34567,7,'USA',67.00,45.99);
INSERT INTO GSINGH.LEDGER VALUES (34568,8,'USA',97.00,89.99);
INSERT INTO GSINGH.LEDGER VALUES (34569,9,'USA',87.00,71.99);
INSERT INTO GSINGH.LEDGER VALUES (34521,10,'USA',17.00,12.99);
INSERT INTO GSINGH.LEDGER VALUES (34567,11,'USA',17.00,11.99);
INSERT INTO GSINGH.LEDGER VALUES (34531,12,'USA',7.00,4.99);
INSERT INTO GSINGH.LEDGER VALUES (34532,13,'USA',7.00,2.99);
INSERT INTO GSINGH.LEDGER VALUES (34533,14,'USA',7.00,1.99);
INSERT INTO GSINGH.LEDGER VALUES (34534,15,'USA',6.00,3.99);
INSERT INTO GSINGH.LEDGER VALUES (34535,16,'USA',7.00,5.99);
INSERT INTO GSINGH.LEDGER VALUES (34536,17,'USA',8.00,6.99);
INSERT INTO GSINGH.LEDGER VALUES (34537,18,'USA',9.00,5.99);
commit;
Now collect stats on them:
EXEC DBMS_STATS.gather_table_stats(USER, 'TRANSACTION', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'ORDERS', cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'LEDGER', cascade => TRUE);
Now lets see how they are partitioned:
set echo off
set pagesize 2000
set long 10000
set linesize 200
set feedback on
column partition_name format a25
column high_value format a85
set echo on
-- show how the partitions were created
col table_name format a20
col ref_ptn_constraint_name format a20
col format partitioning_type a20
col format REF_TABLE a20
select table_name, partitioning_type, ref_ptn_constraint_name
from user_part_tables
where table_name in ('TRANSACTION','ORDERS','LEDGER');
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('TRANSACTION','ORDERS','LEDGER')
order by partition_position, table_name;
select up.table_name, up.partitioning_type, uc.table_name ref_table
from user_part_tables up,
(select r.table_name, r.constraint_name from user_constraints uc, user_constraints r
where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc
where up.ref_ptn_constraint_name = uc.constraint_name(+)
and up.table_name in ('TRANSACTION','ORDERS','LEDGER');
set echo on
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('TRANSACTION')
order by partition_position, table_name
/
--============================================================
Part 2:
Now lets partition the parent table online with a range partition as:
-- Step 1 : Create interium table:
CREATE TABLE GSINGH.TRANSACTION_PAR (
TRANSACTION_ID NUMBER PRIMARY KEY,
TRANSACTION_DATE DATE NOT NULL,
ACCOUNT_ID NUMBER NOT NULL,
DEVICE_ID NUMBER)
PARTITION BY RANGE (TRANSACTION_DATE)
subpartition by hash(DEVICE_ID)
( PARTITION y1 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')),
PARTITION y2 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')),
PARTITION y3 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')),
PARTITION y4 VALUES LESS THAN (TO_DATE('01-JAN-2009', 'DD-MON-YYYY')),
PARTITION y5 VALUES LESS THAN (TO_DATE('01-JAN-2010', 'DD-MON-YYYY')),
PARTITION y6 VALUES LESS THAN (TO_DATE('01-JAN-2011', 'DD-MON-YYYY')),
PARTITION y7 VALUES LESS THAN (TO_DATE('01-JAN-2012', 'DD-MON-YYYY')),
PARTITION y8 VALUES LESS THAN (TO_DATE('01-JAN-2013', 'DD-MON-YYYY')));
-- Step 2. Check if table can be redefined:
EXEC dbms_redefinition.can_redef_table(USER, 'TRANSACTION');
--To get better performance use parallel operations as follows
alter session force parallel query parallel 60;
alter session force parallel query parallel 60;
-- Step 3. Start redefinition of the table, This may take long for big tables and for big tables we should use parallelism:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'TRANSACTION',
int_table => 'TRANSACTION_PAR');
END;
/
Step 4: Create the table constraints and indexes in this section
CREATE INDEX GSINGH.TRANSACTION_PAR_date_ind ON TRANSACTION_PAR(ACCOUNT_ID);
-- Step 5: Gather stats for this table.
EXEC DBMS_STATS.gather_table_stats(USER, 'TRANSACTION', cascade => TRUE);
-- Step 6: Now finish the process of redefinition.
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'TRANSACTION',
int_table => 'TRANSACTION_PAR');
END;
/
-- At this point the interim table has become the "real" table and their names have been switched in the name dictionary.
-- Step 7: Remove original table which now has the name of the interim table:
-- Check the constraints to be dropped
col owner format a20
col CONSTRAINT_NAME format a20
select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from dba_constraints where owner='GSINGH' and table_name in ('TRANSACTION','ORDERS','LEDGER','TRANSACTION_PAR');
--Drop constraints
alter table GSINGH.ORDERS drop CONSTRAINT ORDERS_FK;
alter table GSINGH.LEDGER drop CONSTRAINT LEDGER_FK;
-- Drop table
drop table GSINGH.TRANSACTION_PAR;
-- Add foreign Key constraints and other constraints again
alter table GSINGH.ORDERS add CONSTRAINT orders_fk FOREIGN KEY (TRANSACTION_ID) REFERENCES TRANSACTION;
alter table GSINGH.LEDGER add CONSTRAINT LEDGER_fk FOREIGN KEY (TRANSACTION_ID) REFERENCES TRANSACTION;
-- Rename the Index back to teh original name
ALTER INDEX GSINGH.TRANSACTION_PAR_date_ind RENAME TO TRANSACTION_date_ind;
-- Finally check for what we just did i.e. if the table has repartitioned or not.
SELECT partitioned FROM user_tables WHERE table_name = 'TRANSACTION';
SELECT partition_name FROM user_tab_partitions WHERE table_name = 'TRANSACTION';
--========================================================================================
-- Do the final checks now
set feed on
set pages 100
set line 300
set feed on
select count(*) from user_tab_partitions;
select * from transaction;
select * from orders;
select * from ledger;
select segment_name, partition_name, sum(bytes)/1024/1024 from dba_segments where owner='GSINGH'
and segment_name in ('TRANSACTION','ORDERS','LEDGER')
group by segment_name, partition_name
order by segment_name, partition_name;
set pages 100
-- ===============================================================================
set echo off
set pagesize 2000
set long 10000
set linesize 200
set feedback on
column partition_name format a25
column high_value format a85
set echo on
-- show how the partitions were created
col table_name format a20
col ref_ptn_constraint_name format a20
col format partitioning_type a20
col format REF_TABLE a20
select table_name, partitioning_type, ref_ptn_constraint_name
from user_part_tables
where table_name in ('TRANSACTION','ORDERS','LEDGER');
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('TRANSACTION','ORDERS','LEDGER')
order by partition_position, table_name;
select up.table_name, up.partitioning_type, uc.table_name ref_table
from user_part_tables up,
(select r.table_name, r.constraint_name from user_constraints uc, user_constraints r
where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc
where up.ref_ptn_constraint_name = uc.constraint_name(+)
and up.table_name in ('TRANSACTION','ORDERS','LEDGER');
set echo on
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('TRANSACTION')
order by partition_position, table_name
/
-- ===============================================================================
Part 3: Now lets partition Child table 1 as reference partition on Parent Table:
-- Reference Partitioning of the child table table
CREATE TABLE GSINGH.ORDERS_PAR (
TRANSACTION_ID NUMBER NOT NULL,
ORDER_ID NUMBER PRIMARY KEY,
PRICE NUMBER,
QUANTITY NUMBER,
CONSTRAINT orders_fk_par FOREIGN KEY (TRANSACTION_ID) REFERENCES TRANSACTION
)
PARTITION BY REFERENCE (orders_fk_par);
-- Check if it can be redefined
EXEC dbms_redefinition.can_redef_table(USER, 'ORDERS');
--To get better performance use parallel operations as follows
alter session force parallel query parallel 60;
alter session force parallel query parallel 60;
--Start redefinition of the table, This may take long for big tables and for big tables we should use parallelism:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'ORDERS',
int_table => 'ORDERS_PAR');
END;
/
-- Step 5: Gather stats for this table.
EXEC DBMS_STATS.gather_table_stats(USER, 'ORDERS_PAR', cascade => TRUE);
--Now finish the process of redefinition.
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'ORDERS',
int_table => 'ORDERS_PAR');
END;
/
-- At this point the interim table has become the "real" table and their names have been switched in the name dictionary.
-- Remove original table which now has the name of the interim table:
-- Drop table
drop table GSINGH.ORDERS_PAR;
-- Rename the constraint
select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from dba_constraints where owner='GSINGH' and table_name='ORDERS' and CONSTRAINT_TYPE='R';
begin
for r in (select CONSTRAINT_NAME,TABLE_NAME from dba_constraints where owner='GSINGH' and table_name='ORDERS' and CONSTRAINT_TYPE='R') loop
execute immediate 'alter table '
r.TABLE_NAME
' rename constraint '
r.CONSTRAINT_NAME
' to orders_fk';
end loop;
end;
/
select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from dba_constraints where owner='GSINGH' and table_name='ORDERS' and CONSTRAINT_TYPE='R';
set echo off
set pagesize 2000
set long 10000
set linesize 200
set feedback on
column partition_name format a25
column high_value format a85
set echo on
-- show how the partitions were created
col table_name format a20
col ref_ptn_constraint_name format a20
col format partitioning_type a20
col format REF_TABLE a20
select table_name, partitioning_type, ref_ptn_constraint_name
from user_part_tables
where table_name in ('TRANSACTION','ORDERS','LEDGER');
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('TRANSACTION','ORDERS','LEDGER')
order by partition_position, table_name;
select up.table_name, up.partitioning_type, uc.table_name ref_table
from user_part_tables up,
(select r.table_name, r.constraint_name from user_constraints uc, user_constraints r
where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc
where up.ref_ptn_constraint_name = uc.constraint_name(+)
and up.table_name in ('TRANSACTION','ORDERS','LEDGER');
set echo on
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('TRANSACTION')
order by partition_position, table_name
/
-- ===============================================================================
Now lets partition the second child table as reference partition of the parent table as:
-- Reference Partitioning of the second child table table
CREATE TABLE GSINGH.LEDGER_PAR (
LEDGER_ID NUMBER PRIMARY KEY,
TRANSACTION_ID NUMBER NOT NULL,
CURRENCY_CODE varchar(3),
PAYMENT_TOTAL NUMBER,
BALANCE NUMBER,
CONSTRAINT LEDGER_fk_par FOREIGN KEY (TRANSACTION_ID) REFERENCES TRANSACTION
)
PARTITION BY REFERENCE (LEDGER_fk_par);
-- Check if it can be redefined
EXEC dbms_redefinition.can_redef_table(USER, 'LEDGER');
--To get better performance use parallel operations as follows
alter session force parallel query parallel 60;
alter session force parallel query parallel 60;
--Start redefinition of the table, This may take long for big tables and for big tables we should use parallelism:
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => USER,
orig_table => 'LEDGER',
int_table => 'LEDGER_PAR');
END;
/
-- Step 5: Gather stats for this table.
EXEC DBMS_STATS.gather_table_stats(USER, 'LEDGER_PAR', cascade => TRUE);
--Now finish the process of redefinition.
BEGIN
dbms_redefinition.finish_redef_table(
uname => USER,
orig_table => 'LEDGER',
int_table => 'LEDGER_PAR');
END;
/
-- At this point the interim table has become the "real" table and their names have been switched in the name dictionary.
-- Remove original table which now has the name of the interim table:
-- Drop table
drop table GSINGH.LEDGER_PAR;
-- Rename the constraint
select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from dba_constraints where owner='GSINGH' and table_name='LEDGER' and CONSTRAINT_TYPE='R';
begin
for r in (select CONSTRAINT_NAME,TABLE_NAME from dba_constraints where owner='GSINGH' and table_name='LEDGER' and CONSTRAINT_TYPE='R') loop
execute immediate 'alter table '
r.TABLE_NAME
' rename constraint '
r.CONSTRAINT_NAME
' to ledger_fk';
end loop;
end;
/
select owner,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS from dba_constraints where owner='GSINGH' and table_name='LEDGER' and CONSTRAINT_TYPE='R';
set echo off
set pagesize 2000
set long 10000
set linesize 200
set feedback on
column partition_name format a25
column high_value format a85
set echo on
-- show how the partitions were created
col table_name format a20
col ref_ptn_constraint_name format a20
col format partitioning_type a20
col format REF_TABLE a20
select table_name, partitioning_type, ref_ptn_constraint_name
from user_part_tables
where table_name in ('TRANSACTION','ORDERS','LEDGER');
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('TRANSACTION','ORDERS','LEDGER')
order by partition_position, table_name;
select up.table_name, up.partitioning_type, uc.table_name ref_table
from user_part_tables up,
(select r.table_name, r.constraint_name from user_constraints uc, user_constraints r
where uc.constraint_name=r.constraint_name and uc.owner=r.owner) uc
where up.ref_ptn_constraint_name = uc.constraint_name(+)
and up.table_name in ('TRANSACTION','ORDERS','LEDGER');
set echo on
select table_name, partition_name, high_value
from user_tab_partitions
where table_name in ('TRANSACTION')
order by partition_position, table_name
/
-- ===============================================================================
Thats it ! Tables have been redefined as reference partition online. For this I used Oracle Note: 472449.1
In the dwelling of the womb, there is no ancestry or social status. All have originated from the Seed of God.
Tell me, O Pandit, O religious scholar: since when have you been a Brahmin? Don’t waste your life by continually claiming to be a Brahmin. Pause . If you are indeed a Brahmin, born of a Brahmin mother, then why didn’t you come by some other way? How is it that you are a Brahmin, and I am of a low social status? How is it that I am formed of blood, and you are made of milk? Says Kabeer, one who contemplates God, is said to be a Brahmin among us.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment