RE: possible to use an exceptions table in parallel?

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: "'Niall Litchfield'" <niall.litchfield@xxxxxxxxx>, <tim@xxxxxxxxx>
  • Date: Tue, 24 Jun 2008 08:48:22 -0400

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





From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx] 
Sent: Tuesday, June 24, 2008 7:40 AM
To: tim@xxxxxxxxx
Cc: mwf@xxxxxxxx; jkstill@xxxxxxxxx; ricks12345@xxxxxxxxx;
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. 




On Tue, Jun 24, 2008 at 5:37 AM, Tim Gorman <tim@xxxxxxxxx> 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

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.







From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jared Still
Sent: Monday, June 23, 2008 2:31 PM
To: ricks12345@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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) */

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;



Niall Litchfield
Oracle DBA 

Other related posts: