From shirish@rolta.com Wed, 27 Jun 2001 03:37:43 -0700
From: "Shirish Khapre" <shirish@rolta.com>
Date: Wed, 27 Jun 2001 03:37:43 -0700
Subject: RE: DUPLICATE VALUE CHECK
Message-ID: <F001.00339A8D.20010627034040@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain
Title: RE: DUPLICATE VALUE CHECK



is it 
possible in oracle 8.0.5?
<SPAN 
class=205334610-27062000> 
<SPAN 
class=205334610-27062000>shirish
<SPAN 
class=205334610-27062000> 

  <FONT face=Tahoma 
  size=2>-----Original Message-----From: root@fatcity.com 
  [mailto:root@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?  <FONT color=#0000ff face=Arial 
  size=2>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@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 <FONT 
    face=Arial size=2>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 <FONT face=Arial 
    size=2>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 <FONT face=Arial 
    size=2>-- Author: Shirish Khapre 
      INET: shirish@rolta.com 
    Fat City Network Services    -- 
    (858) 538-5051  FAX: (858) 538-5051 <FONT face=Arial 
    size=2>San Diego, California        -- 
    Public Internet access / Mailing Lists <FONT face=Arial 
    size=2>-------------------------------------------------------------------- 
    To REMOVE yourself from this mailing list, send 
    an E-Mail message to: 
    ListGuru@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 <FONT face=Arial 
    size=2>also send the HELP command for other information (like 
    subscribing).