Delete Records from Oracle 8i goes VERY slow

Dev Shed Forums Sponsor:

December 28th, 2003, 10:04 AM

dries_neyrinck

Junior Member

Join Date: Dec 2003

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

Delete Records from Oracle 8i goes VERY slow

OK, first of all, I'm not an experienced Oracle Developer, I write mostly in .NET and my current project involves an Oracle 8i database.

We have a production database where a lot of production results are stored in 4 tables t_car_hmi, t_comps_hmi, t_bolts_hmi and t_results_hmi.
Every car can have a lot of comps, every comp can have a lot of bolts, and every bolt can have a lot of results so not very complicated I guess...

We add results via SQL (builded up based on an XML file) and this goes very smoothly.
However, If we want to remove entries from this tables, we have some major speed issues...

I wrote this little procedure based on for loops that goes trough each table

Code:

BEGIN 
  FOR SERIAL_ID IN (SELECT SERIAL_ID FROM T_CAR_HMI WHERE T_CAR_HMI.MIX >= 
'0464730' AND T_CAR_HMI.MIX <= '0464739') LOOP 
    FOR COMPS_ID IN (SELECT COMPS_ID FROM T_COMPS_HMI WHERE 
T_COMPS_HMI.CAR_ID = SERIAL_ID.SERIAL_ID) LOOP 
      FOR BOLTS_ID IN (SELECT BOLTS_ID FROM T_BOLTS_HMI WHERE 
T_BOLTS_HMI.COMPS_ID = COMPS_ID.COMPS_ID) LOOP 
          DELETE FROM T_RESULTS_HMI WHERE T_RESULTS_HMI.BOLTS_ID = 
BOLTS_ID.BOLTS_ID; 
          DELETE FROM T_BOLTS_HMI WHERE T_BOLTS_HMI.BOLTS_ID = 
BOLTS_ID.BOLTS_ID; 
      END LOOP; 
      COMMIT; 
      DELETE FROM T_COMPS_HMI WHERE T_COMPS_HMI.COMPS_ID = 
COMPS_ID.COMPS_ID; 
    END LOOP; 
    COMMIT; 
    DELETE FROM T_CAR_HMI WHERE T_CAR_HMI.SERIAL_ID = SERIAL_ID.SERIAL_ID; 
  END LOOP; 
  COMMIT; 
END;

This took a minute to complete...

I tried then the following procedure (after rewriting the data to the DB)

Code:

BEGIN
  DELETE FROM t_results_hmi 
  WHERE t_results_hmi.bolts_id in
  (SELECT t_bolts_hmi.bolts_id
  FROM t_bolts_hmi, 
         t_comps_hmi , 
         t_car_hmi 
  WHERE t_bolts_hmi.comps_id = t_comps_hmi.comps_id 
  AND t_comps_hmi.car_id = t_car_hmi.serial_id 
  AND t_car_hmi.mix >= '0464730' 
  AND t_car_hmi.mix <= '0464739');
  COMMIT;

  DELETE FROM t_bolts_hmi 
  WHERE t_bolts_hmi.comps_id in
  (SELECT t_comps_hmi.comps_id 
  FROM   t_comps_hmi , 
         t_car_hmi 
  WHERE t_comps_hmi.car_id = t_car_hmi.serial_id 
  AND t_car_hmi.mix >= '0464730' 
  AND t_car_hmi.mix <= '0464739'); 
  COMMIT;

  DELETE FROM t_comps_hmi 
  WHERE t_comps_hmi.car_id in
  (SELECT t_car_hmi.serial_id
  FROM t_car_hmi 
  WHERE t_car_hmi.mix >= '0464730' 
  AND t_car_hmi.mix <= '0464739'); 
  COMMIT; 

  DELETE FROM t_car_hmi 
  WHERE t_car_hmi.mix >= '0464730' 
  AND t_car_hmi.mix <= '0464739'; 
  COMMIT; 
END;

It took over 4 minutes to complete...

Does anyone have an idea how to increase the speed, because we did this test with 10 records while we have thousands of records in a day...

Thanks;
Dries

December 28th, 2003, 05:15 PM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 8th Plane (8500 - 8999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,527

Time spent in forums: 3 Months 1 Week 1 Day 3 h 19 m 49 sec
Reputation Power: 537

Surely all those nested queries slow it down.
You should use inner joins instead (not for the last block DELETE FROM t_car_hmi ...).
Note that if you need to empty the tables the fastest way is to truncate.

Delete Records from Oracle 8i goes VERY slow

Developer Shed Advertisers and Affiliates