RE: possible to use an exceptions table in parallel?

I haven't had a chance to test whether a unique or primary key constraint enforced by a non-unique index performs differently from one enforced by an index created as unique, so I've always been reticent to suggest it. Has anyone else tested this thoroughly? I'm actually disappointed that the syntax "alter index <index_name> unique" still does not exist so the keyword would be apparent on index reports. However, unless there is a material difference in performance of the index/constraint pair versus the unique index, his suggestion is indeed a big improvement in the protocol (and adding the constraint is always good documentation).  

If it turns out there is a material difference, the additional option is to add a gratuitous column to the original non-unique index (probably using the shortest average value), so that the subsequent creation of the unique index has the opportunity to read the existing index in order if the optimizer judges that is cheaper than reading the table. All this would be moot if altering the index unique was possible. Of course if there is no material difference in performance it is already moot except for the index reports. It is entirely possible that by the time it gets to enforcement it is a single code path, anyway. Someone please chime in if you've examined this.  

Even if there is a difference in the enforcement cost, the sheer savings of the duplicate index creation could dwarf any future enforcement cost increases, especially if future of the table is slowly changing.  

Thanks Tim and Niall  

mwf  

From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Tuesday, June 24, 2008 7:40 AM
To: tim_at_evdbt.com
Cc: mwf_at_rsiz.com; jkstill_at_gmail.com; ricks12345_at_gmail.com; oracle-l_at_freelists.org
Subject: Re: possible to use an exceptions table in parallel?  

Indeed, and arguably it makes more sense from a maintenance point of view to use a non-unique index to enforce uniqueness using constraints since the index won't get dropped if the constraint is for various maintenance procedures. I'm not sure I actually buy this argument mind you.    

Niall

On Tue, Jun 24, 2008 at 5:37 AM, Tim Gorman <tim_at_evdbt.com> wrote:

It wouldn't necessarily be wasted effort to create the non-unique index. You can create a PRIMARY KEY or UNIQUE KEY constraint on a non-unique index. So, go ahead and create the non-unique index in parallel, find your duplicates using a GROUP BY ... HAVING COUNT(*) > 1, and then when they've been cleaned up go ahead and create the PK/UK constraint over the non-unique index.

Mark W. Farnham wrote:

One more thing: Creating the relevant non-unique index (in parallel if you like) should allow the relevant "group by having" query to run very quickly, and since you're interested in listing out the exceptions you might want to stuff min(rowid) and my_col values having count > 1 into a table from which to drive the row copies(optional) and deletes before creating the unique index. True, it will cost you to create the non-unique version of the index, but since you'll probably be running across it multiple times to check for, possibly copy, and delete duplicates it should be a net win. Using min(rowid) is one choice for what to keep, if you have other unique keys different from my_col already enforced on the table with an index a min or max on that column might also be a good choice, and of course someone might demand an arbitrary obtuse choice system for which row to retain.  

Regards,  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still
Sent: Monday, June 23, 2008 2:31 PM
To: ricks12345_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: possible to use an exceptions table in parallel?  

Should you wish to try this method, the parallel hint syntax was incorrect.

It should be /*+ parallel (my_tab,4) */

<snip>

Theoretically you can add a parallel hint to the query to run it in parallel

select /*+ parallel my_tab(4) */ my_col
from my_tab
group by my_col
having_count > 1;

<snip>

• http://www.freelists.org/webpage/oracle-l

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info 




--
http://www.freelists.org/webpage/oracle-l