thanks i was hoping for an insert select... but I would have to do a bulk collect into a pl/sql table, then forall inserts into the table.

problem is I dont have enough RAM to store all the deltas, so in some cases I may have to do 2-4 bulk collects.

this seems like it might be alot faster than an anti-join. Much faster actually. anyone try this?

btw, if I bulk collect data into an array in memory, how does it get deprecated from memory? I would assume it goes into the buffer cache? but is it considered a full table scan and gets bumped quickly?
>
> From: "Quintin, Richard" <[Email Address Removed] Date: 2003/05/30 Fri PM 12:50:32 EDT
> To: <[Email Address Removed]
> "LazyDBA.com Discussion" <[Email Address Removed] Subject: RE: RE: how does oracle manage unique constraints?
>
> Yep. Bulk binds. Tom gives a great example here:
> http://asktom.oracle.com/pls/ask/f?p=4950:8:235256812809254164::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3675533064673,
>
> -----Original Message-----
> From: [Email Address Removed] [mailto:[Email Address Removed] Sent: Friday, May 30, 2003 12:46 PM
> To: LazyDBA.com Discussion
> Subject: Re: RE: how does oracle manage unique constraints?
>
>
> so every time you do an insert oracle does a unique index scan to search for duplicatse. If none exists, insert proceeds.
>
> Im wondering if there is a way for me to take advantage of this with SQL instead of doing an anti-join.
>
> I have a master table. I also have a staging table with delta records. This table contains insert,update,and delete records. Our flags for insert records are not accurate so we are forced to do a constly anti-join that is expensive and slow.
>
> Now I can probably avoid the anti-join(though I have not tested the performance yet) by using a cursor and pl/sql then calling an insert procedure that will throw an exception when the insert fails due to a unique constraint.
>
> however, Im in version 8i, and context switching between pl/sql and sql is very costly. Anyway to write an array insert statement that insert records and if it hits a unique constraint just skip the record and proceed?
>
> any kind of trick I can use?
> >
> > From: "Quintin, Richard" <[Email Address Removed] > Date: 2003/05/30 Fri PM 12:37:30 EDT
> > To: <[Email Address Removed]
> > "LazyDBA.com Discussion" <[Email Address Removed] > Subject: RE: how does oracle manage unique constraints?
> >
> > It does it by creating a B Tree index.
> > http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88856/c11schem.htm#3353
> >
> >
> > -----Original Message-----
> > From: [Email Address Removed] [mailto:[Email Address Removed] > Sent: Friday, May 30, 2003 12:31 PM
> > To: LazyDBA.com Discussion
> > Subject: how does oracle manage unique constraints?
> >
> >
> > Anyone have an internals doc or info on what oracle does to ensure PKs and unique constraint remain unique? I cant find it in any of the documentation. All I get is the high level stuff that says it does it and you dont have to worry about it.
> >
> > Dont need to know. Just curious.
> >
> >
> > --------
> > Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> > To unsubscribe: send a blank email to oracledba-[Email Address Removed] > To subscribe: send a blank email to oracledba-[Email Address Removed] > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> > Tell yer mates about http://www.farAwayJobs.com
> > By using this list you agree to these terms:http://www.lazydba.com/legal.html
> >
> >
>
>
> --------
> Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>

Oracle LazyDBA home page