Tighten up Procedure 2

[cwsstins]

I've got the following code running in a procedure, called by a KSH script. It does what it needs to do (delete dup records), but it takes a long time to complete. I realize there's some redundancy here, but I can't seem to get it to work any other way I've tried. I would appreciate help in tightening up the code:

    PROCEDURE Delete_Dups IS
   BEGIN
    DELETE FROM Equipment 
     WHERE Sequence NOT IN
     (
      SELECT MAX(Sequence)
       FROM Equipment e1
        WHERE e1.CustID||SerialNo = CustID||SerialNo
         GROUP BY CustID||SerialNo
          HAVING COUNT(*) > 1
       )
    and
     CustID||SerialNo IN 
     (
      SELECT CustID||SerialNO
       FROM Equipment e1
        GROUP BY CustID||SerialNo
         HAVING COUNT(*) > 1
      );
    commit;
   END Delete_Dups;

 

[SantaMufasa]

Stinsman, Here is how I would write the code to do what you want. (The first section of code, below, displays the rows that the second section will delete.):

Code that displays ROWID and CustID||SerialNO for rows that will be deleted:

select rowid, CustID||SerialNO
  from equipment outer
 where rowid < (select max(rowid)
                  from equipment inner
                 where inner.CustID||SerialNO = 
                       outer.CustID||SerialNO))
/

Code that deletes all duplicates except for one row:

delete from equipment where rowid in (
select rowid
from equipment outer
where rowid < (select max(rowid)
                 from equipment inner
                where inner.CustID||SerialNO = 
                      outer.CustID||SerialNO))
/

Let us know if this both resolves your need and is faster.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

Hi Santa,

For some reason, that statement select a whole lot more records, some of them being the max that I want to keep. This brings back 315 records, whereas the original only brings back 89 records.

Something else I'm curious about...when I run the original code above directly in PL/SQL it executes in about 45 seconds. When I call it as a procedure through a KSH script it takes longer than several minutes, at which point I cancel it.

 

[SantaMufasa]

When we haven't tables and sample data with which to work, sometimes our "ivory-tower" efforts fall short.

Here is another "ivory-tower" SQL statement to try:

select rowid, CustID||SerialNO x
  from equipment outer
 where sequence < (select max(sequence)
                     from equipment inner1
                    where inner1.CustID||SerialNO = 
                          outer.CustID||SerialNO)
   and exists (select 'x' from equipment inner2
                where inner2.CustID||SerialNO = 
                       outer.CustID||SerialNO)
/

Let us know if this is better.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cwsstins]

Thanks Santa...helpful as always.

 

[SantaMufasa]

Stinsman,

I was just reviewing the code, above, and I forgot to properly qualify "SerialNO". You will want to use the slightly adjusted code (with modifications in bold):

select rowid, CustID||SerialNO x
  from equipment outer
 where sequence < (select max(sequence)
                     from equipment inner1
                    where inner1.CustID||[b]inner1.[/b]SerialNO = 
                          outer.CustID||[b]outer[/b].SerialNO)
   and exists (select 'x' from equipment inner2
                where inner2.CustID||[b]inner2.[/b]SerialNO = 
                       outer.CustID||[b]outer.[/b]SerialNO)
/

Sorry, but the "ivory tower" rears its ugly battlement once more.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

Surely the main problem is that weird concatenation of CustID and SerialNo. This will prevent any indexes being used on those columns. Why can you not do:

select rowid, CustID||SerialNO x
  from equipment outer
 where sequence < (select max(sequence)
                     from equipment inner1
                    where inner1.CustID = outer.CustID
                      and inner1.SerialNo = outer.SerialNo )
   and exists (select 'x' from equipment inner2
                where inner2.CustID  = outer.CustID
                  and inner2.SerialNO = outer.SerialNO)
/

 

[cwsstins]

Well, ultimately I wound up ditching this method anyway in favor of finding a way to mark records as Add or Change and then insert or update each record. The original plan was to insert everything and delete the dups.

But I will throw a start atcha for some useful info you provided, that I didn't know...that concatenation prevents the indexes from being used.