Re: Using SQL*Loader to update rows FAST - help!

herbyderby_at_my-deja.com wrote in message <919273$hvb$1_at_nnrp1.deja.com>...
>A full table scan takes about 30 seconds. Each row has about 10 NUMBER
>columns, nothing fancy.
>
>My reasons for SQL*Loader were:
>
>1) The work to calculate the new column values is a lot of repetitive
>math and is currently written in pl/sql and uses a bunch of joins. I
>think that it can be done faster by exporting all the data and doing
>the calculations and sorting in C, and then loading the rows back in.
>I've heard that even Java is a better option than pl/sql for high
>performance stored procedures like this, except that as far as I know,
>Java cannot yet use bulk bind/collect, FORALL, etc.
>
>2) We can use NOLOGGING or UNRECOVERABLE to eliminate that unnecessary
>(for us) I/O.
>
>3) There are a bunch of indexes on the table and SQL*Loader will
>postpone index maintenance until the end of the run (although I guess
>we could do this ourselves by disabling/enabling?).

Your arguments for using SQL*Loader seem valid to me.

I'm loading quite a bit of data into my database every night using SQL*Loader. The number of rows loaded per second varies considerably with the length of each row and the number of columns in each row. For instance, one table takes 90 seconds to load 0.6 million rows, whilst another takes 20 seconds to load 0.5 million rows.

Your table is very simple and has short rows. It should load very fast.

You are right that SQL*Loader can't do updates, so I'd unload the entire table, manipulate just the rows which require it, and then load the whole 4 million rows back in using SQL*Loader with the truncate option. Personally, I would drop any indexes beforehand and then rebuild them using CREATE INDEX commands afterwards, rather than let SQL*Loader do it.

Working on my values, you'll probably be able to load 4 million rows in about 4 minutes, YMMV.

Dave.

--
If you reply to this newsgroup posting by email, remove the "nospam"
from my email address first.