Home » SQL & PL/SQL » SQL & PL/SQL » Partition Exchange - Performance (Oracle 11.2.0.2.0)
Partition Exchange - Performance [message #590401] Wed, 17 July 2013 13:13 Go to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi All,
I have a requirement to migrate some tables from one schema to another.
The tables are range partitioned and each table has around 680 million records.
I'm using EXCHANGE PARTITION feature to move the data.
The problem is for some of the partitions this is taking around 10 mins or more.
Test Case.
-- Schema A
DROP TABLE t_main;
CREATE TABLE T_MAIN 
   (	F_ID           NUMBER, 
        F_CREATED_TS   TIMESTAMP (6), 
	 CONSTRAINT PK_I_MAIN PRIMARY KEY (F_ID, F_CREATED_TS) ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS 
 PARTITION BY RANGE (F_CREATED_TS) 
 (
 PARTITION PT_20120630  VALUES LESS THAN (TIMESTAMP' 2012-07-01 00:00:00') , 
 PARTITION PT_20120731  VALUES LESS THAN (TIMESTAMP' 2012-08-01 00:00:00') , 
 PARTITION PT_20120831  VALUES LESS THAN (TIMESTAMP' 2012-09-01 00:00:00') , 
 PARTITION PT_20120930  VALUES LESS THAN (TIMESTAMP' 2012-10-01 00:00:00') , 
 PARTITION PT_20121031  VALUES LESS THAN (TIMESTAMP' 2012-11-01 00:00:00') , 
 PARTITION PT_20121130  VALUES LESS THAN (TIMESTAMP' 2012-12-01 00:00:00') , 
 PARTITION PT_20121231  VALUES LESS THAN (TIMESTAMP' 2013-01-01 00:00:00') , 
 PARTITION PT_20130131  VALUES LESS THAN (TIMESTAMP' 2013-02-01 00:00:00') , 
 PARTITION PT_20130228  VALUES LESS THAN (TIMESTAMP' 2013-03-01 00:00:00') , 
 PARTITION PT_20130331  VALUES LESS THAN (TIMESTAMP' 2013-04-01 00:00:00') , 
 PARTITION PT_20130430  VALUES LESS THAN (TIMESTAMP' 2013-05-01 00:00:00') , 
 PARTITION PT_20130531  VALUES LESS THAN (TIMESTAMP' 2013-06-01 00:00:00') , 
 PARTITION PT_20130630  VALUES LESS THAN (TIMESTAMP' 2013-07-01 00:00:00') , 
 PARTITION PT_20130731  VALUES LESS THAN (TIMESTAMP' 2013-08-01 00:00:00') , 
 PARTITION MAX_PART  VALUES LESS THAN (MAXVALUE) )
 /

INSERT INTO t_main VALUES (1, SYSDATE - 360);
INSERT INTO t_main VALUES (2, SYSDATE - 330);
INSERT INTO t_main VALUES (3, SYSDATE - 300);;
INSERT INTO t_main VALUES (4, SYSDATE - 270);
INSERT INTO t_main VALUES (5, SYSDATE - 240);
INSERT INTO t_main VALUES (6, SYSDATE - 210);
INSERT INTO t_main VALUES (7, SYSDATE - 180);
INSERT INTO t_main VALUES (8, SYSDATE - 150);
INSERT INTO t_main VALUES (9, SYSDATE - 120);
INSERT INTO t_main VALUES (10, SYSDATE - 90);
INSERT INTO t_main VALUES (11, SYSDATE - 60);
INSERT INTO t_main VALUES (12, SYSDATE - 30);
INSERT INTO t_main VALUES (13, SYSDATE - 5);
INSERT INTO t_main VALUES (14, SYSDATE + 30);
COMMIT
/
grant select, alter on T_MAIN to B;

-- Schema B
-- Staging table 
DROP TABLE T_TEMP;
CREATE TABLE T_TEMP 
   (	F_ID           NUMBER, 
        F_CREATED_TS   TIMESTAMP (6), 
	 CONSTRAINT PK_I_MAIN_1 PRIMARY KEY (F_ID, F_CREATED_TS) ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS 
/

------
-- Target table in schema B
DROP TABLE t_main;

CREATE TABLE T_MAIN 
   (	F_ID           NUMBER, 
        F_CREATED_TS   TIMESTAMP (6), 
	 CONSTRAINT PK_I_MAIN PRIMARY KEY (F_ID, F_CREATED_TS) ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS 
 PARTITION BY RANGE (F_CREATED_TS) 
 (
 PARTITION PT_20120630  VALUES LESS THAN (TIMESTAMP' 2012-07-01 00:00:00') , 
 PARTITION PT_20120731  VALUES LESS THAN (TIMESTAMP' 2012-08-01 00:00:00') , 
 PARTITION PT_20120831  VALUES LESS THAN (TIMESTAMP' 2012-09-01 00:00:00') , 
 PARTITION PT_20120930  VALUES LESS THAN (TIMESTAMP' 2012-10-01 00:00:00') , 
 PARTITION PT_20121031  VALUES LESS THAN (TIMESTAMP' 2012-11-01 00:00:00') , 
 PARTITION PT_20121130  VALUES LESS THAN (TIMESTAMP' 2012-12-01 00:00:00') , 
 PARTITION PT_20121231  VALUES LESS THAN (TIMESTAMP' 2013-01-01 00:00:00') , 
 PARTITION PT_20130131  VALUES LESS THAN (TIMESTAMP' 2013-02-01 00:00:00') , 
 PARTITION PT_20130228  VALUES LESS THAN (TIMESTAMP' 2013-03-01 00:00:00') , 
 PARTITION PT_20130331  VALUES LESS THAN (TIMESTAMP' 2013-04-01 00:00:00') , 
 PARTITION PT_20130430  VALUES LESS THAN (TIMESTAMP' 2013-05-01 00:00:00') , 
 PARTITION PT_20130531  VALUES LESS THAN (TIMESTAMP' 2013-06-01 00:00:00') , 
 PARTITION PT_20130630  VALUES LESS THAN (TIMESTAMP' 2013-07-01 00:00:00') , 
 PARTITION PT_20130731  VALUES LESS THAN (TIMESTAMP' 2013-08-01 00:00:00') , 
 PARTITION MAX_PART  VALUES LESS THAN (MAXVALUE) )
 /

I'm using the below statements for moving the data from table A.T_MAIN to B.T_MAIN
-- Schema B
alter table A.T_MAIN exchange partition PT_20120630 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20120630 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20120731 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20120731 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20120831 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20120831 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20120930 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20120930 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20121031 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20121031 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20121130 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20121130 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20121231 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20121231 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20130131 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20130131 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20130228 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20130228 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20130331 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20130331 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20130430 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20130430 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20130531 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20130531 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20130630 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20130630 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition PT_20130731 with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition PT_20130731 with table B.T_TEMP  without validation ;
--
alter table A.T_MAIN exchange partition MAX_PART with table B.T_TEMP  without validation ;
alter table B.T_MAIN exchange partition MAX_PART with table B.T_TEMP  without validation ;


This test case works fine, but with actual tables for some partitions, exchange partition takes more time.
Could you please help me in resolving this issue.


Thank you.

[Updated on: Wed, 17 July 2013 13:14]

Report message to a moderator

Re: Partition Exchange - Performance [message #590403 is a reply to message #590401] Wed, 17 July 2013 13:25 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query v$session and v$lock to know on what the session is waiting.
Or activate SQL trace for this session and analyze it.
We can't say more as we have nothing to diagnose.

Regards
Michel
Re: Partition Exchange - Performance [message #590435 is a reply to message #590403] Thu, 18 July 2013 04:58 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Hi,
The extra time is spent on rebuilding the index on the target table.
Is there any way to reduce this time.


Thank you.
Re: Partition Exchange - Performance [message #590437 is a reply to message #590435] Thu, 18 July 2013 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the index on the target table and create them after all moved have been done.

Regards
Michel
Re: Partition Exchange - Performance [message #590439 is a reply to message #590435] Thu, 18 July 2013 05:22 Go to previous messageGo to next message
John Watson
Messages: 4863
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Hi,
The extra time is spent on rebuilding the index on the target table.
Is there any way to reduce this time.


Thank you.
Remember that it isn't merely rebuilding the index: it IS an index. Can you try reversing the order of the columns in the key, to f_created_ts followed by f_id? No promises, but I would put the partitioning key as the leading component of the index key. I haven't seen it done any other way.
Re: Partition Exchange - Performance [message #590453 is a reply to message #590439] Thu, 18 July 2013 05:50 Go to previous messageGo to next message
yuko
Messages: 65
Registered: August 2011
Member
Michel,
Cant remove the PK from the target table as it is IOT.

John,
Got the below error when tried to change the order of the columns in the key.
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
Re: Partition Exchange - Performance [message #590456 is a reply to message #590453] Thu, 18 July 2013 05:58 Go to previous messageGo to next message
John Watson
Messages: 4863
Registered: January 2010
Location: Global Village
Senior Member
Quote:
John,
Got the below error when tried to change the order of the columns in the key.

ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
So fix your code. It works for me.
Re: Partition Exchange - Performance [message #590469 is a reply to message #590456] Thu, 18 July 2013 06:27 Go to previous message
yuko
Messages: 65
Registered: August 2011
Member
John,
I didn't get you.
Can you please elaborate.

Thank you.
Previous Topic: problem with having clause
Next Topic: Removing duplicate in Hierarchy Level
Goto Forum:
  


Current Time: Sun Dec 21 06:32:05 CST 2014

Total time taken to generate the page: 0.09428 seconds