Not ins are nasty, try something more like:
create table tmp (
dup_valule varchar(100),
keep_row rowid);
create index t1 on tmp(dup_value);
insert into tmp
select my_column_name, max(rowid) from my_table
group by my_column_name
having count(*) > 1);
select my_table.*
from my_table, tmp
where my_column_name = dup_value
and my_table.rowid != keep_row;
Or, if you are just trying to clean up:
delete from my_table_name
where rowid in (
select min(rowid) from my_table
group by my_column_name
having count(*) > 1);
If a duplicate has more than two occurances, this would need to run multiple
times. Not the best solution for the first case, but will probably be the
fastest for the substring case as you will make a single pass through the
table, performing a single substr on each row.
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
-----Original Message-----
Sent: Tuesday, June 26, 2001 10:42 AM
To: Multiple recipients of list ORACLE-L
Hi all
i have one column in my table (in which daily 100000 rows are added to the
table) which has values like
XYZ_A_LO0000_0000001
XYZ_A_LO0000_0000002
XYZ_A_LO0000_0000003
XYZ_A_LO0000_0000004
XYZ_A_LO0000_0000005
XYZ_A_LO0000_0000006
XYZ_A_LO0000_0000007
XYZ_A_LO0000_0000008
XYZ_A_LO0000_0000009
i want to check duplicate values.. there are 2 cases of duplication
Case I :- i am using the following query
select from my_table where rowid not in(
select max(rowid) from my_table
group by my_column_name );
i am getting the rows which are duplicate ..
CASE II : - i want to check duplication in last 7 characters(which are
actually nos) in my column like
0000001
0000002
0000003....so on....
i am using substr function to get this value and i am checking the values
with remaining rows..
but as the table contains nearly 450000(present rowcount) the query is
taking lot of time...which i can't afford...
the column has index on it.
plz suggest me what to do??
Shirish Khapre, SE Rolta India Ltd.
Off Ph No. (+91) (022) 8326666,8262222,8300568
Ext'n 2730
Minds are like parachutes. They only function when they are open
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Shirish Khapre
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
