Re: SQL*Loader - using direct load path with a table during others performing delete/select

On 23 Jun 2003 02:57:06 -0700, vikingil_at_yahoo.com (viktor) wrote:

>Hi All,
>Thanks Sybrand for answering this fast.
>I just need more detailed answer:
>1. By using direct path load and not the conventional path load I
>think all the data is written above high water mark. It is updated
>only after the load is over. Shouldn't this free me from using staging
>table and partition my target table?

No definitely not. Usually data coming from external systems is just a piece of junk, and it needs cleaning to conform to the RI constraints in your schema (I am aware most developers don't use constraints and just use their database as a big garbage bin)

>2. If I won't use staging table what happens when user perform
>select/update/insert or even truncate table during the load?

If you commit in between (and likely you are, as sql*loader definitely won't commit once unless you specify a sufficient arraysize) you are in danger of the other process updating/deleting the junk you just inserted.

>Might he receive partial data or is it atomic operation?
>3. It might be possible to generate the data file for the load
>according the index I got on the table (and use "SORTED INDEXES" in
>the ctl file).

>This improves performance and also maybe a reason not to use the stage
>table since the data is already sorted so no "load with no index then
>build the index on staging table" is needed.

Nonsense. The sorted index feature is only useful on *INITIAL LOAD* only.

>4. Is there any other danger not using the staging table? I just want
>to keep is as simple as possible...

Yes. Getting an inconsistent mess in your database. But who cares? As your data is inconsistent the end-user just won't see the orphaned records, eh?

>Thanks,
>Viktor
>

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jun 23 2003 - 12:49:29 CDT