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?
2. If I won't use staging table what happens when user perform
select/update/insert or even truncate table during the load?
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.
4. Is there any other danger not using the staging table? I just want
to keep is as simple as possible...
Thanks,
Viktor
Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<b1gbfvgaog5oaabiam76j7hdbq1pdoi0da_at_4ax.com>...
> On 22 Jun 2003 05:39:05 -0700, vikingil_at_yahoo.com (viktor) wrote:
>
> >Hi All,
> >I have the following problem.
> >We are using SQL*Loader in conventional mode.
> >I want to use the direct mode in order to improve the performance.
> >Does any one can tell me if there are any problems with the following:
> >1. Issuing select / insert/ delete on the table (which has index)
> >during the direct path load operation?
> >2. Is the SQL loader operation is atomic from user point of view
> >meaning might he receive partial data from the load during the index
> >update / high-water mark update after the load is over?
> >If not how can it be modified to be atomic? Should I use staging
> >tables? and How do I use them - I happened to stumble about this issue
> >in one article but I don't know how it works (yet :-) ).
> >3. If there is no way to make SQL loader operation atomic how can user
> >be prevented from accessing to the table during the load?
> >4. I think users might need to remove old / bad data from time to time
> >might it cause problems during direct path load operation.
> >5. Any other known problems when using SQL*Loader?
> >Thanks ,
> >Viktor
>
> You have just summed up about all the reasons to use staging tables
> Just load the data into staging tables with identical definition of
> the target tables. Clean up your data in the staging phase, making
> sure proper constraints exist on the target tables. Then just insert
> select and there you go.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Mon Jun 23 2003 - 04:57:06 CDT
