RE: DUPLICATE VALUE CHECK

From: Norrell, Brian <BNorrell_at_QuadraMed.com>
Date: Tue, 26 Jun 2001 09:37:26 -0700
Message-ID: <F001.00338667.20010626084629@fatcity.com>

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: shirish_at_rolta.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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Norrell, Brian
INET: BNorrell_at_QuadraMed.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------