I should mention that you're going to incur a context switch (two really) for every row that fails so if you have a bunch of them, you might be better off using an anti-join.
I haven't compared the performance of this technique to anti-join, so I can't comment on which is faster. Test it out and let us know.
-----Original Message-----
From: Quintin, Richard
Sent: Friday, May 30, 2003 12:51 PM
To: LazyDBA.com Discussion
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] 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] subscribe: send a blank email to oracledba-[Email Address Removed] 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] subscribe: send a blank email to oracledba-[Email Address Removed] 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