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

Well, sloppiness is contagious, actually. Oracle's own training materials suffer the same problem on this particular topic!

First, what the books say, prominently on one of the slides is: "Direct Loads will enforce only Primary Key, Unique and Not Null constraints". And, sure enough, if you read the small print, we see that "Foreign Key abd check constraints are disabled and can be enabled at the end of the run by using the appropriate commands in the (sql loader) control file."

Fair enough: we presume from this that at least Primary, Unique and Not Null constraints *will* be enforced. Trouble is, it all depends what you mean by the word "enforce". Dig a tad deeper, and this lovely bit of text appears:

"Primary key and unique constraints are checked during and at the end of the run, and may be disabled if they are violated".

So, if nothing violates the constraints, fine. Otherwise, the constraints are switched off, and the violating data is still loaded into the table. In either case, the load completes regardless, and you've got some manual checking on the constraint state to do, and if you discover it's been disabled, you've got some manual "enforcement" to do at the end of the load when it comes time to switch the constraints back on.

This is some new meaning of the word "enforce" that I've never come across before!!

Regards
HJR "TurkBear" <john.greco_at_dot.state.mn.us> wrote in message news:he8mpu0hhdve95fo9ac9u05q2uvrih0oon_at_4ax.com...
>
> 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 - 15:21:58 CDT