Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Loading Data...

Re: Loading Data...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 16 Mar 1999 11:54:07 GMT
Message-ID: <36f145b2.4420326@192.86.155.100>


A copy of this was sent to "steve" <sjm26_at_hotmail.com> (if that email address didn't require changing) On Tue, 16 Mar 1999 20:41:33 +1100, you wrote:

>I need to load a few of million rows into an empty database (the biggest
>tables will require between 1 and 2 million each). The rows are currently
>sitting in flat files....
>
>What is generally the best strategy for approaching this task. I have used a
>program called BCP under Sybase that seemed to work pretty well - Is there
>an Oracle equivalent? Is it any good?
>

the program is sqlldr. would recommend

direct path mode is very similar to bcp (but faster...) no triggers or constraints will be fired

>If I consider a row-by-row approach using a c program is it likely to finish
>within the millenium? I will be dropping all indexes and triggers before
>starting.
>
>I appreciate your comments and experience.
>
>steve.
>
>(Also; Is there an Oracle equivalent of the...
>
>UPDATE A SET A.X = B.X FROM B WHERE A.KEY = B.KEY
>
>statement I used to use in Sybase???)
>

there are 2 general syntaxes for that:

SQL> create table t1 ( a int primary key, b varchar2(25) ); Table created.

SQL> create table t2 ( x int , y varchar2(25) ); Table created.

SQL> insert into t1 values ( 1, 'table 1 data' ); SQL> insert into t1 values ( 2, 'table 1 data' );

SQL> insert into t2 values ( 1, 'table 2 data' );
SQL> insert into t2 values ( 2, 'table 2 data' );
SQL> insert into t2 values ( 3, 'table 2 data' );
SQL> commit;

Commit complete.

SQL> select * from t2;

         X Y

---------- -------------------------
         1 table 2 data
         2 table 2 data
         3 table 2 data

SQL> update ( select * from t1, t2 where t2.x = t1.a )   2 set y = b
  3 /
2 rows updated.

SQL> select * from t2;

         X Y

---------- -------------------------
         1 table 1 data
         2 table 1 data
         3 table 2 data

SQL> rollback;

Rollback complete.

SQL>
SQL> update t2
  2 set y = ( select b from t1 where t1.a = t2.x )   3 where x in ( select a from t1 )
  4 /

2 rows updated.

SQL> select * from t2;

         X Y

---------- -------------------------
         1 table 1 data
         2 table 1 data
         3 table 2 data

 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Mar 16 1999 - 05:54:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US