-
remove duplicates
Hi,
I have a table with 50 million rows. The table has 4 columns, where the 4 columns make up a unique row.
Unfortunately, duplication has occurred on 500 thousand rows.
I need to remove the duplicates and leave one row intact per duplication.
I've looked at various ways of doing this, but does anyone have any experience of the quickest way??
Would I be better off recreating the table (with a dedupe statement) or sticking with the delete??
I'd appreciate any examples.....
Thanks.
-
I'm looking at using this.....
Code:
delete from HORACE
where rowid in
( select rid
from ( select rowid rid,
row_number() over
(partition by account_org
,account_number
,statement_id_code
,statement_sequence_number
order by rowid) rn
from HORACE
)
where rn <> 1 );
Which I've modified from a single column version by Mr. Kyte
-
The delete is taking way too long.
What syntax would I use to create another table from the original table, but with the duplicates removed?
-
SQL> Alter system execute developers;
I remember when this place was cool.
-
Originally posted by Mr.Hanky
SQL> Alter system execute developers;
wtf?!@!@!%$!!!!!
PHP Code:
SQL> Alter system execute developers;
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
ORA-02342: you must purchase the advanced 'Kill all the developers' package to use this feature
I'm stmontgo and I approve of this message
-
-
Originally posted by stmontgo
wtf?!@!@!%$!!!!!
PHP Code:
SQL> Alter system execute developers;
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
ORA-02342: you must purchase the advanced 'Kill all the developers' package to use this feature
A good DBA is always prepared, you should have planned for this in your impact assessment and budgeted accordingly.
I remember when this place was cool.
-
hi hunie,
if ur table is partitioned, remove the duplicates by 1 partition
range at a time. Try this one...
Delete from table1 partition (1)
where rowid in
(select max(rowid) from table1 partition (1)
group by col1,col2,col3,col4 having count(*)>1)
/
-
Thanks for the advice guys,
We have been using Quest's SQL Terminator application, but developers are 10 a penny, so the problem is not going away.
The table is not partitioned, so that's not an option.
Thanks for the link Davey, useful for identifying the duplicates, but no help in leaving a single valid row in the offending table, or in recreating a new table minus the duplication.
-
up to you to decide which row to delete then instead of deleting the wrong one by accident
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|