RE: DUPLICATE VALUE CHECK

From: Shirish Khapre <shirish_at_rolta.com>
Date: Wed, 27 Jun 2001 03:37:43 -0700
Message-ID: <F001.00339A8D.20010627034040@fatcity.com>

is it
possible in oracle 8.0.5?
<SPAN

class=205334610-27062000>
<SPAN

class=205334610-27062000>shirish
<SPAN

class=205334610-27062000>

-----Original Message-----From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Koivu, LisaSent: Wednesday, June 27, 2001 12:37 AMTo: Multiple recipients of list ORACLE-LSubject: RE: DUPLICATE VALUE CHECK Hi Shirish, FWIW:
 Function-based index? Reverse
 index? for case II? Have you considered either of these options to ease the pain of that query?
 HTH <FONT color=#0000ff

  face=Arial size=2>Lisa Koivu <FONT color=#0000ff face=Arial 
  size=2>Clumsy Ninja-ette (WAaaaaaaaaaaaa!) <FONT color=#0000ff 
  face=Arial size=2>Ft. Lauderdale, FL, USA

-----Original Message----- <FONT

    face=Arial size=1>From:   <FONT face=Arial 
    size=1>Shirish Khapre [SMTP:shirish_at_rolta.com] <FONT 
    face=Arial size=1>Sent:   <FONT face=Arial 
    size=1>Tuesday, June 26, 2001 11:42 AM <FONT face=Arial 
    size=1>To:     <FONT face=Arial 
    size=1>Multiple recipients of list ORACLE-L <FONT face=Arial 
    size=1>Subject:        <FONT 
    face=Arial size=1>DUPLICATE VALUE CHECK

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 <FONT face=Arial 
    size=2>XYZ_A_LO0000_0000002 <FONT face=Arial 
    size=2>XYZ_A_LO0000_0000003 <FONT face=Arial 
    size=2>XYZ_A_LO0000_0000004 <FONT face=Arial 
    size=2>XYZ_A_LO0000_0000005 <FONT face=Arial 
    size=2>XYZ_A_LO0000_0000006 <FONT face=Arial 
    size=2>XYZ_A_LO0000_0000007 <FONT face=Arial 
    size=2>XYZ_A_LO0000_0000008 <FONT face=Arial 
    size=2>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: <A href="http://www.orafaq.com" target=_blank>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).

Received on Wed Jun 27 2001 - 05:37:43 CDT