Re: What is different between sql*load(direct=y) and insert /+*append */

Both tools load data into tables above the high watermark, and only move the high watermark above the 'data line' at the end of the operation (when you commit or SQL Loader does a data save). Since data is above the high watermark for the duration of the load, using either tool, it is impossible to read that data (that's what the HWM's job is, after all: telling full table scans when to stop looking for data).

If it's impossible to read the data, then there is no need for either tool to generate rollback, which is (putting it simply) only needed for the purposes of constructing read-consistent images of the data *for other Users' benefit*. They can't see the data, therefore they don't need read-consistent images of it, therefore no rollback.

So far, so similar.

The difference between the two tools is what happens on indexes. SQL Loader Direct Loads don't update indexes. Append inserts do. Which means that insert appends are required to generate rollback on the indexes, because the changes it makes to them during the insert operation *are* visible to other users, and hence they need to roll them back for read-consistency purposes.

Regards
HJR "David" <dhs_69_at_hotmail.com> wrote in message news:7e64a3ea.0210011044.706e0261_at_posting.google.com...
> Hi,
>
> We used to use sql*load with direct=y option to load
> our clickstream data,in order to get better performance,
> we recently use " insert /+* append */ "
> approach, but we got "rollback segment too small" error.
>
> how sql*load never got this problem ? what is the different
> between two approachs in oracle?
>
> Thanks
> David
Received on Tue Oct 01 2002 - 15:02:40 CDT