Home » RDBMS Server » Performance Tuning » Merging large amounts of data into large tables (oracle 10)
Merging large amounts of data into large tables [message #338351] Mon, 04 August 2008 12:49 Go to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
i need to merge(using a merge statement) 2.5 million rows of data from one table into another table that is 143 million rows. I want this to take under 30 mins. However it seems to take at least 2 hours to do this.
I was wondering if it would be faster to merge in small amounts of data in a loop. Because i noticed most of the wait time was I/O i was thinking oracle does not handle large amounts of data well.

Does anyone else know of a faster way to merge 2.5 million rows into a table fast. i have to update some rows and insert others that is why i am using a merge.
Re: Merging large amounts of data into large tables [message #338352 is a reply to message #338351] Mon, 04 August 2008 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

You have the tables.
You have the data.
You have the SQL.

We have a report it is taking longer than you desire/expect/need.

Why do you expect a miracle solution based upon NO useful information?

You're On Your Own (YOYO)!
Re: Merging large amounts of data into large tables [message #338363 is a reply to message #338351] Mon, 04 August 2008 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do it in just one SQL, this is much faster.

Regards
Michel
Re: Merging large amounts of data into large tables [message #338365 is a reply to message #338352] Mon, 04 August 2008 13:54 Go to previous messageGo to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
i was looking for methods not so much sql hacks. i have tools that can tune those. i have a hundred or so sql statements that all work pretty much the same. But if you need a sql example then here it is:
MERGE INTO flrkpcuspos B
USING (
select aaacfan,
aaipcus,
aabdte,
aashrbal,
osaor,
osregc,
aatacfan,
aarfln,
aafardte,
aaimpsrc,
aadivopt
from fwimportrkp9
where aarfln = a_filename
and errorind = '0'
and dupind = 'N'
and aaptpe = 'P') E
ON (B.acfan = E.aaacfan
and B.ipcus = E.aaipcus
and B.prbdte = E.aabdte
and B.prtacfan = E.aatacfan)
WHEN MATCHED THEN
UPDATE SET B.PRSHRBAL = e.aashrbal,
B.PRAOR = e.osaor,
B.PRREGC = e.osregc ,
B.PRIMPSRC = e.aaimpsrc,
B.PRAUO = a_UserName,
B.PRAUD = sysdate
WHEN NOT MATCHED THEN
INSERT (B.ACFAN,
B.IPCUS,
B.PRBDTE,
B.PRSHRBAL,
B.PRAUO,
B.PRAUD,
B.PRAOR,
B.PRREGC,
B.PRTACFAN,
B.PRRFLN,
B.PRFARDTE,
B.PRIMPSRC,
B.PRDIVOPT)
VALUES(E.aaacfan,
E.aaipcus,
E.aabdte,
E.aashrbal,
a_UserName,
sysdate,
E.osaor,
E.osregc,
E.aatacfan,
E.aarfln,
E.aafardte,
E.aaimpsrc,
E.aadivopt );

table structure:
CREATE TABLE FWIMPORTRKP9
(
AARFLN CHAR(12 BYTE),
ACCTNO CHAR(20 BYTE),
AAIPCUS CHAR(9 BYTE),
AAPTPE CHAR(1 BYTE) DEFAULT 'P',
AABDTE DATE,
AASHRBAL NUMBER(16,6) DEFAULT 0,
AASHBI CHAR(1 BYTE) DEFAULT '1',
AALOIE NUMBER(16,6) DEFAULT 0,
AAREGC CHAR(3 BYTE) DEFAULT '000',
AAFARDTE DATE,
AAIMPSRC CHAR(10 BYTE),
ROWINFILE NUMBER(10) DEFAULT 0,
ERRORIND CHAR(1 BYTE) DEFAULT '0',
DUPIND CHAR(1 BYTE) DEFAULT 'N',
AAACFAN CHAR(20 BYTE),
AATACFAN CHAR(20 BYTE),
OSAOR CHAR(20 BYTE),
OMEFFDTE DATE,
OSREGC CHAR(3 BYTE) DEFAULT 000,
AADIVOPT CHAR(1 BYTE)
)
TABLESPACE FLWORK
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE FLRKPCUSPOS
(
ACFAN CHAR(20 BYTE) NOT NULL,
IPCUS CHAR(9 BYTE) NOT NULL,
PRBDTE DATE NOT NULL,
PRSHRBAL NUMBER(16,6) DEFAULT 0 NOT NULL,
PRAUO VARCHAR2(15 BYTE) NOT NULL,
PRAUD DATE NOT NULL,
PRAOR CHAR(20 BYTE) NOT NULL,
PRREGC CHAR(3 BYTE) DEFAULT '000' NOT NULL,
PRTACFAN CHAR(20 BYTE) NOT NULL,
PRFARDTE DATE NOT NULL,
PRRFLN CHAR(12 BYTE),
PRIMPSRC CHAR(10 BYTE),
PRDIVOPT CHAR(1 BYTE)
)
TABLESPACE FLMAST
PCTUSED 0
PCTFREE 15
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 16K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


Re: Merging large amounts of data into large tables [message #338366 is a reply to message #338363] Mon, 04 August 2008 13:56 Go to previous messageGo to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
not sure what you mean by one sql? the merge statement i used is one sql. But it is taking over 2 hours to run. i posted the merge statement in my post. for 2.5 million rows it takes a while.
Re: Merging large amounts of data into large tables [message #338367 is a reply to message #338351] Mon, 04 August 2008 13:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
table fwimportrkp9 needs recent statistics
& columns aarfln, errorind, dupind, aaptpe need indexes & current statistics.
Re: Merging large amounts of data into large tables [message #338368 is a reply to message #338367] Mon, 04 August 2008 14:04 Go to previous message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
ironic that you bring that up about statistics on that table. We cannot get current stats on the table due to a oracle bug with the CBO. if we use the correct statistics we get incorrect results. we report the issue to oracle and they said that they had a bug with it.
but i guess i could add indexs to that table.
Previous Topic: Need suggestion for a better query.
Next Topic: DB link query.
Goto Forum:
  


Current Time: Thu Dec 05 13:15:18 CST 2024