what's the best way to use to insert millions of rows into a table.

the insertion should occurred once a month. the data gets selected from another five tables. Is there anything what would improve the performance? I mean is there anything else to do except use select /insert?
JelaVAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
fastest way is to NOT do it.  

May seem obvious, but is it really necessary to copy the data from the other 5 tables?
Can you simply query the 5 when needed?

If you do need to (either functionally or for query performance) then, no,  insert select is the fastest way.  
You might be able to help by using the append hint on the insert
or improving the queries that hit the other 5 tables.

0
Tomac_YaoCommented:
I THANK IT IS SLOW USING SELECT INSERT ONLY. YOU CAN load Data very fast using Partition Exchange

PLEASE REFER THE FOLLOW LINK.

http://www.akadia.com/services/ora_exchange_partition.html


0
Franck PachotOracle DBACommented:
Hi,

The fastest way is:
 - insert from select
 - in direct-path with append hint (to generate no undo for table changes)
 - making indexes unusable and rebuild them after (to generate no undo for indexes changes)
 - table in nologging if you can afford table loss before the next backup (to generate no logging for table change when insert is in direct-path)
 - rebuild indexes in nologging to generate no redo for index changes

If you destination table is well partitioned, you should have to make only the concerned index partitions as unusable.

Regards,
Franck.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

schwertnerCommented:
Your task is a normal process and should be done.
The biggest stopper of inserts are indexes and logging processes.

1. Try to disable the indexes for the time of the insert. If the indexes are connected to constraints (primary key, unique) then try to analyze the data in the tables in advance.

To disable logging:
insert /*+APPEND */ into ... select ... from [some other table or view].
But the table must be defined (or altered) to "nologging".
0
arctanxCommented:
Here is a good one, we used this method, use the SQLLDR utility.  Set yourself up a CTL file, look here for example:

http://www.verio.com/support/documents/view_article.cfm?doc_id=1513

Then set up a .bat file with this information:

sqlldr USER/PASS@DATABASE control="YOUR PATH HERE" data="YOUR FILE HERE"

pause

Then set the bat file on the scheduler to run once per month. We never had any problems using this method, and loaded serveral million rows.
0
sdstuberCommented:
sqlldr assumes the source data to be loaded is in a file, in this case though the source data is coming from 5 tables already in the database.
0
arctanxCommented:
Right you are, I missed that part.
0
sdstuberCommented:
glad I could help,  sqlldr is very efficient if the data was in a file though
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.