Re: possible to use an exceptions table in parallel?

On Mon, Jun 23, 2008 at 6:24 AM, Rick Ricky <ricks12345_at_gmail.com> wrote:

> http://www.jlcomp.demon.co.uk/faq/duplicates.html
>
> I do not know how to do an
>
> 'alter table add unique constraint' in parallel.
>
> the only way I know how to use parallel is with a 'create index'. Problem
> is 'exceptions into' does not seem to work with this statement.
>
> I have a big table. Anyway to use the exceptions table with the parallel
> clause? if not it will run for hours.
>

It appears that you are using the EXCEPTIONS clause just to locate duplicates.

If so, you may be able to use another method.

You could use select with group by to find the duplicates

select my_col
from my_tab
group by my_col
having_count > 1;

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;

I say theoretically because I don't see a parallel query happening when I use it on test 10gR2 db.

When I modify the table with 'alter table tablename parallel 4' I do get the

parallel query.

Modifying the table is likely not an option however.

Getting the parallel hint to work may require some research.

The 10gR2 docs say the hint will override the table settings, but I don't see it happening.

Probably an oversight on my part, but this is just to give you an idea for an alternative method anyway.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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