Re: possible to use an exceptions table in parallel?
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Tue, 24 Jun 2008 12:40:16 +0100
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@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
> 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@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <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) */*
>
> <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>
>
> -- //www.freelists.org/webpage/oracle-l
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
