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:
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