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 LazyDBA home page