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 <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