Home » RDBMS Server » Performance Tuning » Large table help (inserting into large tables.)
Large table help [message #273273] Tue, 09 October 2007 16:12 Go to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
I have a table which I need to insert into every day about 500,000 new rows. The table is about 80 million now. Which is about 12 gigs of data. The insert used to run fast when the table was small but now that the table is large it is taking hours.
I tried the insert with the append hint. I also tried the merge statement it did not speed it up. It needs to run in under 2 hours. But it is taking a second or two per insert and that time is growing as the table is growing.
Is there any way to do a fast insert into a very large table?
or is there some setting that we have to set to work with large tables?
any ideas would be helpful. the insert just selects from one table into another. The table it is selecting from never grows it is always about 600,000 rows.
Re: Large table help [message #273274 is a reply to message #273273] Tue, 09 October 2007 16:17 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Re: Large table help [message #273285 is a reply to message #273273] Tue, 09 October 2007 18:48 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
way too little here to help you. try posting some code. Sounds to me like you are leaving something out of your description. I would wager you are scanning some significant amount of data because your "simple" insert is not simple.

Insert speed all depends upon your environment, but using a decent yet not overly fast box, I was able to achieve 1 million rows inserted per minute to a wide row using a somewhat sophisticated extract query. So your 500,000 rows should load pretty fast.

Again, post some code, and a query plan.

In the meantime I will check asktomhome to see if there are any reports on inserts slowing as tables grow.

good luck, Kevin
Re: Large table help [message #273286 is a reply to message #273273] Tue, 09 October 2007 18:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
tom says maybe its your log file on raid5. Are your logs on raid5?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3679447698936

good luck, Kevin
Re: Large table help [message #273305 is a reply to message #273286] Tue, 09 October 2007 22:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Here's some tips on fast inserts.

If you are still having problems, run a SQL Trace and post the TKPROF output.

Ross Leishman
Re: Large table help [message #273426 is a reply to message #273285] Wed, 10 October 2007 07:33 Go to previous messageGo to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
like i said it is a very simple select statement
i have tried different hints to try to speed it up with big tables but nothing works.

insert into flpos
(ACFAN,
IPCUS,
PRBDTE,
PRSHRBAL,
PRAUO,
PRAUD,
PRAOR,
PRREGC,
PRTACFAN,
PRRFLN,
PRfardte,
PRIMPSRC,
PRDIVOPT)
(select /*+ PARALLEL(FWIMPORTRKP9,DEFAULT) */ aaacfan,
aaipcus,
aabdte,
aashrbal,
a_UserName,
sysdate,
osaor,
osregc,
aatacfan,
aarfln,
aafardte,
aaimpsrc,
aadivopt
from fwimportrkp9
where aarfln = a_filename
and errorind = '0'
and dupind = 'N'
and aaptpe = 'P'
and not exists
(select '1' from flpos
where ACFAN = aaacfan
and IPCUS = aaipcus
and prtacfan = aatacfan
and PRBDTE = aabdte));
thanks for all the help...
Re: Large table help [message #273438 is a reply to message #273426] Wed, 10 October 2007 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Large table help [message #273509 is a reply to message #273426] Wed, 10 October 2007 13:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
off the top of my head I notice that:

You are inserting into the same table you are selecting from. Maybe this has something to do with it. Possibly the rebuilding of modified index pages or something... I have heard of similar stories on earlier versions of Oracle where this was inefficient. May I suggest a simple test. Try loading a temp table with the half-million rows you want to insert, then insert them for real from the temp table. It sounds silly that moving the same data twice should be faster than moving it once, but then again, who knows. Also, can you verify that your existential sub-query is supported by the proper index
Re: Large table help [message #273531 is a reply to message #273509] Wed, 10 October 2007 14:49 Go to previous messageGo to next message
zzasteven
Messages: 18
Registered: August 2007
Junior Member
i see what you are saying about the selecting from a table where we are inserting. i shall change that. strange that it ran fast on tables of 40 million. the SQL uses the indexes on the table. But the larger the table the longer it takes.
I have never heard of moving it to a temp table and then moving into the real table. I shall have to try that.
thanks
for your help and input!
Re: Large table help [message #273725 is a reply to message #273531] Thu, 11 October 2007 09:01 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Hope it works.

I am not saying the code is wrong. Heck I write this kind of code all the time. But when you start moving lots of data, small quirks become bigger issues.

The idea of a temp table is just somehting a lot of people do when moving data. It seems a common practice among many to create waypoints in a data translation process.

When I said temp table, I didn't actually mean a "global temporary table", any waypoint table would work. I was just thinking of this:

create table work1
(
  a number, b number, c number
)
/

create table real1
(
  a number, b number,c number
)
/

insert into work1
select a,b,c from real1
/

insert into real1 select * from work1
/


this takes the heat of the real table. Seems silly, but I bet it makes a difference to you.

Good luck, Kevin
Previous Topic: Updaing a 804 million partioned table (for 365 days) from a 67 million table
Next Topic: View not using index on underlying tables
Goto Forum:
  


Current Time: Sun Dec 04 10:50:40 CST 2016

Total time taken to generate the page: 0.04247 seconds