Re: find duplicate records (?)

From: Thomas Heimann <NOSP-heimann-edv_at_t-online.de>
Date: Thu, 26 Jul 2001 09:24:22 +0200
Message-ID: <3B5FC5A6.476CE1A8_at_t-online.de>

Haplo wrote:
>
> Hi Friends...
>
> I have one big problem. I need to find all duplicate records in two
> tables with circ. 30.000 records, thise tables have 12 & 21 columns. How to
> do it fast. (PL/SQL procedure)
>
> Many thanks !!!

Hi Haplo,

try something like this:

---

PROCEDURE find_duplicates IS

TYPE dup_array_typ IS TABLE OF tabname%rowtype INDEX BY BINARY_INTEGER; CURSOR dup_cur IS

   SELECT col1,
          col2,
          col3,
          col4,
          col5,
          col6,
          col7,
          col8,
          col9 

   FROM tabname ORDER BY 2,3,4,5,6,7,8,9;    dup_array dup_array_typ;

BEGIN
  OPEN dup_cur;

  FETCH dup_cur INTO dup_array(0);
  FETCH dup_cur INTO dup_array(1);
  WHILE dup_cur%FOUND LOOP
     IF dup_array(0).col1=dup_array(1).col1 and
        dup_array(0).col2=dup_array(1).col2 and
        dup_array(0).col3=dup_array(1).col3 and
        dup_array(0).col4=dup_array(1).col4 and
        dup_array(0).col5=dup_array(1).col5 and
        dup_array(0).col6=dup_array(1).col6 and
        dup_array(0).col7=dup_array(1).col7 and
        dup_array(0).col8=dup_array(1).col8 AND
        dup_array(0).col9=dup_array(1).col9 THEN
--------------------------------------------------------------------------------

-- Action for a found duplicate

---

      ELSE
        dup_array(0):=dup_array(1);
      END IF;
      FETCH dup_cur INTO dup_array(1); 

   END LOOP;
   CLOSE dup_cur;
END ;

---

Col1 is unique in this example.

Thomas Received on Thu Jul 26 2001 - 09:24:22 CEST