Re: possible to use an exceptions table in parallel?

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 23 Jun 2008 22:37:04 -0600
Message-ID: <486079F0.50900@evdbt.com>

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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jared Still
Sent: Monday, June 23, 2008 2:31 PM
To: ricks12345@gmail.com
Cc: oracle-l@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 Received on Mon Jun 23 2008 - 23:37:04 CDT