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  |
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 #338365 is a reply to message #338352] |
Mon, 04 August 2008 13:54  |
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 #338368 is a reply to message #338367] |
Mon, 04 August 2008 14:04 |
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.
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 22:17:47 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
