Re: SQLLDR performance?

There is a flag you can use to append to your sqlldr command line: sqlldr userid=$DBADMINID/$DBADMINPW control=$TMP_DIR/$ptablename.octl log=$TMP_DIR/$ptablename.log bad=$TMP_DIR/$ptablename.bad rows=10000 bindsize=1048576 > $OUTFILE direct=y

This flag will load data much quicker, however there are some drawbacks: 1. While the load is happening, your triggers on that table get disabled. If you are using a trigger to manipulate data on the way into the table, the trigger won't fire.

2. The sqlload puts your indexes in a "direct load state" where you can not drop the indexes while the load is occurring.

3. You can't use the direct=y flag when you are loading from a client machine. (Maybe not your case, but others may be interested)

>I'm use sqlldr to reload some data. I think it's taking too long
>(around 4 hours for 1,000,000 rows). I've tried upping the bindsize, so
>it's commiting around 65 rows at a time. How much higher can I push
>this value? I'm running Oracle 8.0.4 on an Ultra 5 w/512 MB RAM and
>Solaris 2.6.1.
>
>Here's the command I'm using right now:
>sqlldr userid=$DBADMINID/$DBADMINPW control=$TMP_DIR/$ptablename.octl
>log=$TMP_DIR/$ptablename.log bad=$TMP_DIR/$ptablename.bad rows=10000
>bindsize=1048576 > $OUTFILE
Received on Fri Jan 29 1999 - 16:17:56 CST