Re: select 10000 rows from Oracle in shortest way

On Mon, 23 Jan 2006 12:17:14 +0100, Thomas Kellerer wrote:

> On 23.01.2006 11:27 dok wrote:
>> Looking for a good desicions for trasfering more than 10000 rows from
>> oracle to mysql using only jdbc. Connect is mostly slow about 1 row in
>> a second, rights on oracle are just for select, update, insert
>> operations. I think if I can devide 10000 rows in 10 parts and transfer
>> them in concurent threads and connections. Any suggestion can help me
>> to solve this problem.
>
> I can copy 10000 rows in about 40 seconds (pure JDBC).
>
> But I have noted, that insert speed is extremely slow when autocommit is
> turned on. So maybe that is your problem?
>

Manual commits in a loop with also slow things down.

Another frequent Java coding mistake is to build the SQL string manually
by concat'ing the literals into the string. A knowledgeable ORacle
Java developer will use bind (aka positional) parameters when appropriate.
Under many circumstances building a SQL stateement as a string and
executing that string dynamically is a wonderful way to kill ORacle
performance.

Another thing to look at is bulk binding & array fetching..

All these techniques are well described in the online Oracle Java
Developers and JDBC Developers manuals. Which most Java developers don't
read :-(

--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** Top posting [replies] guarantees I won't respond. ***

.

Relevant Pages

  • Re: Access Oracle Objects via PLSQL from JDBC
    ... In oracle demos I couldn't find a such examples.... ... This example uses a JDBC thin client to call a PLSQL stored ... test.java - this calls the plsql and displays the resultset ... // JDBC Implementation specific connect string ...
    (comp.lang.java.programmer)
  • Re: Query Oracle from Excel
    ... You will need the Oracle client sofware installed on the PC you're running ... What exactly is DBDataProvider and DBDataSource? ... Global prmUsername As String ... Dim adoConn As New ADODB.Connection ...
    (microsoft.public.excel.programming)
  • RE: Query Oracle from Excel
    ... Ron, thanks for your help...this is REALLY helping me!!!! ... Now, let's say that Oracle resides on the LAN/WAN somewhere, and I don't ... Global prmUsername As String ... Dim adoConn As New ADODB.Connection ...
    (microsoft.public.excel.programming)
  • Re: Re-logging into An Oracle Database
    ... I've done it in MS SQL but never in Oracle. ... My connection string seems to work, but the app seems to keep the old userid so I can't see the tables I need to see. ... What I prefer to do is use PTQs is to run updates via VBA, something like this. ... 'no error handling included here for this post, but error procs can be included to return a false for this function of the query does not execute. ...
    (comp.databases.ms-access)
  • RE: $dbh->tables() printing error messages for Oracle under Cygwi n pe rl 5.8
    ... $dbh->tablesprinting error messages for Oracle under ... > Use of uninitialized value in string eq at ... Give a hobbit a ring and he eats fish for an age. ...
    (perl.dbi.users)