Optimize this query [message #22176] |
Thu, 26 September 2002 23:21 |
Kannan
Messages: 29 Registered: September 2000
|
Junior Member |
|
|
Sir
I have a table with 100000 rows in it. I wanted to delete the duplicate rows from it. the query i gave was :
delete from table1 a
where rowid < (select max(rowid) from table1
where code = a.code);
Assuming the code is the primary key. It takes a very long time. How to optimize the query in short time. The table has index on code column
Thanks
Kannan
|
|
|
Re: Optimize this query [message #22191 is a reply to message #22176] |
Fri, 27 September 2002 10:54 |
jie
Messages: 10 Registered: March 2002
|
Junior Member |
|
|
If you just want to delete duplicate record, try this
create table newtable1 as
select distinct * from table1
The new table will no duplicate records. You can truncate the old table, than insert records into table1 from newtable1.
|
|
|