Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

parallel insertion in oracle

 
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all,

I need to insert huge csv file into an oracle database using JDBC . I read somewhere that parallel insert is possible only when the query contains

insert into <tablename> select * from <tablename1> i.e. we can parallely insert from one table to other.

insert into <tablename> values ... ... cannot do a parallel insert.

Now my csv file is very big say around a 1-2 gb. Inserting serially would take day(s) to load it to database which isnt acceptable. Now can I use threading in this case and how ? I have to improve the performance drastically.

Thanks,
Aditya
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The SQL you have doesn't demonstrate a parallel insert and wont really help you speed up your bulk data load.

SQL Loader lets you perform parallel loads. This is where you multi thread the loading task accross available CPUs and disks. Have a read of the SQL Loader documentation They contain a huge wealth of possible performance improvements.
 
Adi Kulkarni
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks Paul,

I could not understand, can you elaborate if possible ?

Thanks,
Aditya
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL Loader is a tool that comes with Oracle whose purpose is to load bulk data in an efficient and configurable way. See the documentation, which also covers parallel loading.
 
Adi Kulkarni
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul / all,

I tried using sql loader for the same. The command run at the console is freezing/hanging indefinitely and no log file is also being populated.

I have tried default options as well still the command is freezing at the console. Can anyone help me on this ?

Thanks,
Aditya
 
Paul Sturrock
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
have you specified a log fiel? SQL Loader ony writes to a log file once a job is complete. How long did you wait to see if the job completed (bulk loading takes time).
 
Adi Kulkarni
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The file was only 33 MB big and I waited for more thatn an hour for that. Also to test I tried it with file of size 2 kb. Still it hangs

My control file is:



The command on the console was :

sqlldr userid=username/password@service control=dlc.ctl
 
Adi Kulkarni
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul,

Can you help me with this ?

Thanks,
Aditya
 
    Bookmark Topic Watch Topic
  • New Topic