If your Duplicates are large in number then the
following method
works fine..
1.
create table TMP as select distinct ( <set of columns
> ) from <ORIGINAL_TABLE>
2. truncate table <ORIGINAL_TABLE>
3. insert into <ORIGINAL_TABLE> select * from TMP/use
SQL Loader after taking a dump from TMP.
Bhulu
- David Wagoner <dwagoner_at_arsenaldigital.com> wrote:
> Here is an interesting script I found on Metalink
> (Note:1019920.6) for
> removing duplicates, but I have not tried it yet:
>
>
>
> ======
> Title:
> ======
>
> Script to Eliminate Non-unique Rows
>
>
> ===========
> Disclaimer:
> ===========
>
> This script is provided for educational purposes
> only. It is NOT supported
> by
> Oracle World Wide Technical Support. The script has
> been tested and appears
>
> to work as intended. However, you should always
> test any script before
> relying on it.
>
> PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to
> differences in the way text
>
> editors, email packages and operating systems handle
> text formatting
> (spaces,
> tabs and carriage returns), this script may not be
> in an executable state
> when
> you first receive it. Check over the script to
> ensure that errors of this
> type are corrected.
>
>
> =========
> Abstract:
> =========
>
> This script removes all but one row (all but the row
> with the highest rowid)
>
> from <owner>.<table> in each group of rows having
> identical values in
> <column(s)>. Multiple columns must be separated
> with commas (without
> spaces).
>
> Script TFSUNIQU is intended primarily for use in
> deleting rows that prevent
> the creation of a unique index on the columns in
> <column(s)>. It will
> happily delete rows that are not identical, as long
> as the rows are
> identical with respect to the values of the columns
> in <column(s)>.
>
>
> =============
> Requirements:
> =============
>
> You must have DELETE privileges on the selected
> table.
>
>
> =======
> Script:
> =======
>
> ----------- cut ---------------------- cut
> -------------- cut --------------
>
>
> SET ECHO off
> REM NAME: TFSUNIQU.SQL
> REM USAGE:"@path/tfsuniqu schema_name table_name
> column_name(s)"
> REM
>
>
> REM REQUIREMENTS:
> REM DELETE on selected table
> REM
>
>
> REM AUTHOR:
> REM Grant Franjione, Phil Joel, and Cary Millsap
>
> REM (c)1994 Oracle Corporation
> REM
>
>
> REM PURPOSE:
> REM Removes all but one row (all but the row with
> the highest rowid)
> REM from <owner>.<table> in each group of rows
> having identical values
> REM in <colum(s)>. Multiple columns must be
> seperated with commas
> REM (without spaces).
> REM
> REM TFSUNIQU is intended primarily for use in
> deleting rows that
> REM prevent the creation of a unique index on the
> columns in
> REM <column(s)>. It will happily delete rows
> that are not identical,
> REM as long as the rows are identical with
> respect to the values of
> REM the columns in <column(s)>.
> REM
>
>
> REM EXAMPLE:
> REM N/A
> REM
>
>
> REM DISCLAIMER:
> REM This script is provided for educational
> purposes only. It is NOT
> REM supported by Oracle World Wide Technical
> Support.
> REM The script has been tested and appears to
> work as intended.
> REM You should always run new scripts on a test
> instance initially.
> REM
>
>
> REM Main text of script follows:
>
> def owner = &&1
> def table = &&2
> def uukey = &&3
>
> delete from &owner..&table
> where rowid in (
> select rowid from &owner..&table
> minus
> select min(rowid) from &owner..&table group by
> &uukey
> )
> /
>
> undef owner
> undef table
> undef uukey
>
>
>
> ----------- cut ---------------------- cut
> -------------- cut --------------
>
>
>
>
>
> David B. Wagoner
> Database Administrator
> Arsenal Digital Solutions Worldwide, Inc.
> 8000 Regency Parkway, Suite 110
> Cary, NC 27511-8582
> Office (919) 466-6723
> Pager 8666864767_at_archwireless.net
> Fax (919) 466-6783
> <http://www.arsenaldigital.com/>
> http://www.arsenaldigital.com/
>
>
> *** NOTICE ***
> This e-mail message is confidential, intended only
> for the named
> recipient(s) above and may contain information that
> is privileged, work
> product or exempt from disclosure under applicable
> law. If you have
> received this message in error, or are not the named
> recipient(s), please
> immediately notify the sender by phone or email and
> delete this e-mail
> message from your computer. Thank you.
>
> -----Original Message-----
> Sent: Tuesday, June 04, 2002 2:54 PM
> To: Multiple recipients of list ORACLE-L
>
> I know I have seen this posted before.......
>
> We have a large range partitioned table that has
> duplicates in it. What is
> the fastest way to remove the dups.? I have the
> following scripts which do
> it but may be fast or slow. What do you guys use?
>
=== message truncated ===
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: S B
INET: bhulubhuli_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sat Jun 08 2002 - 01:23:25 CDT
