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

Oops, Daniel caught me being sloppy again...I should have said does not enforce constraints during the load..

Daniel Morgan <dmorgan_at_exesolutions.com> wrote:

>TurkBear wrote:
>
>> I also believe that 'direct load' ignores any constraints , I'm not sure about appends..
>>
>> "M Hashim" <m.a.n.hashim_at_sympatico.ca> wrote:
>>
>> >clickstream data....yikes!
>> >
>> >Note also, with "direct load" I don't think you could manipulate the data
>> >with any FUNCTIONS as it's being loaded - which you can do otherwise.
>> >
>> >
>> >"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
>> >news:Q3nm9.44168$g9.126416_at_newsfeeds.bigpond.com...
>> >> 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
>> >>
>> >>
>> >
>>
>> -----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
>> http://www.newsfeed.com The #1 Newsgroup Service in the World!
>> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----
>
>Nothing can ignore constraints. The question is rather one of when enforcement takes place.
>
>Daniel Morgan

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Wed Oct 02 2002 - 11:43:48 CDT