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: Using SQL*Loader to update rows FAST - help!

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

From: <herbyderby_at_my-deja.com>
Date: Wed, 13 Dec 2000 23:54:14 GMT
Message-ID: <919273$hvb$1@nnrp1.deja.com>

Thanks Dave,

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?).

Thanks again,
Chris

In article <917to9$gbf$1_at_nnrp1.deja.com>,   Luggy <dgh_consulting_at_my-deja.com> wrote:
> Chris,
>
> SQL*Loader seems like a strange way to do it. Presumably this table
> with 4 million rows is in the database already? How long does it take
> to do a full table scan on the table? You can check this by going into
> SQL*Plus, typing "set timing on", then "select count(1) from
 <table>;".
> If doing the table scan takes longer than you need the update to run
> in, you may well be knackered, I'm afraid.
>
> How big is the column that you need to update, and how big is the
 total
> average row length on the table? Is the column to be updated indexed,
> and if so what kind of index is on it? All these things will have to
 be
> taken into consideration.
>
> Dave.
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Dec 13 2000 - 17:54:14 CST

Original text of this message

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