Re: How to find Duplicate records

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<996134847.8727.0.nnrp-13.9e984b29_at_news.demon.co.uk>...
> Thanks to Frank for taking the trouble to
> supply some feedback; and to Norman
> for updating the FAQ as a consequence.
>
> Remember - no FAQ page should be
> considered complete: if you have further
> useful suggestions, alternative techniques,
> special cases etc. they can be added.
>
> --
> Jonathan Lewis
>
>
> Norman Dunbar wrote in message
> <48B95088F03ED511BE5A00508BEED0881ED1F5_at_tapps.leeds.lfs.co.uk>...
> >Frank,
> >
> >>> "SQL> create table new_test nologging
> >>> 2 as (select /* +APPEND */ distinct * from test);
> >
> >looks like a typo to me :o( - I suspect Oracle treats it as a comment. I
> >can't imagine what I had been drinking/sniffing/injecting when I wrote
> >that in SQL*Plus !
> >I'll update the faq page and send it to Jonathan - thanks.
> >
> >As for the index bit, I suspect you are correct - I'll have to give it a
> >try. I stopped trying to make (my own procedures which merge data from
> >two tables in two different databases into one, then delete the
> >duplicates) more efficient when the biggest table had its deletions done
> >in 30 seconds rather than 45+ minutes. My system now works in total in
> >half an hour, while the original (Supplied by a product supplier) took
> >over 4 hours !
> >
> >Regards,
> >Norman.
> >
> >(This is what happens when you have to do your own proof reading !)
> >

I think the append hint was correct for systems that use the PQO and have it set as a default to parallize. It should be ignored for systems not using PQO so I do not think it hurt. With all the possible options and features of Oracle I think it is getting harder to write code that is correct for all systems.

From my notes on hints: APPEND Only for use with insert statement using parallel query causing new rows to be appended to table in new extents

• Mark D Powell --