Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g49g2000cwa.googlegroups.com!not-for-mail From: "AlexK" Newsgroups: comp.databases.oracle.server Subject: Loading 300 million records Date: 6 Jun 2005 08:40:47 -0700 Organization: http://groups.google.com Lines: 19 Message-ID: <1118072447.344657.22030@g49g2000cwa.googlegroups.com> NNTP-Posting-Host: 65.115.235.109 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1118072453 949 127.0.0.1 (6 Jun 2005 15:40:53 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 6 Jun 2005 15:40:53 +0000 (UTC) User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: g49g2000cwa.googlegroups.com; posting-host=65.115.235.109; posting-account=YxSnCwwAAADxdfKBnmdl9IOoPtG4Visz Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:244343 Hi All, I'm trying to improve the performance of a procedure that loads approximately 300 million records into a table. Currently the process is using 'INSERT /*+ APPEND*/ .. INTO SELECT' and takes about 10 hours to run on 10G. The select joins about 5 tables, most of which are small except the driving table which has about 300M records in it. I believe the indexes are good as explain plan only shows the main table getting a full table scan. From what I've read online, it says that the 'INSERT .. INTO SELECT' is the fastest and most efficient way to load data from one table to another. Unfortunately the only examples I've seen where the quantity of records inserted are mentioned only deals with about 1 million records at most. Is this still the best approach to take when loading 300M records? Would a bulk collect or something else be better since so many records are being processed? Any information would be greatly appreciated. Thanks in advance. Alex