Super long Delete

[tansdot]

I have a delete that takes xtremely long :

DELETE FROM temp_det
WHERE EXISTS (
SELECT 1
FROM temp_orders A,
orig_cap T
WHERE
A.DT = temp_det.DT AND
A.DT = T.DT AND
A.Prod_ID = temp_det.Prod_ID AND
T.Prod_ID = temp_det.Prod_ID AND
T.orig >= temp_det.orig)
Number of rows

temp_det 872745 rows
temp_orders 8444478 rows
orig_cap 126028 rows

INDEXES

temp_orders has 2 indexes
idxa-- dt
idxb-- prod_id
idxc--st,prod_id

temp_det
idxd-- dt,prod_id
idxe--dt

Is there a way to tune this to reduce the number of
hours to run this? This process whenever run , runs as the only process allowed in the database. Can We somehow disable the rollback and redo ? Is it a good idea when noone else can access the database to disable redo & rollback?

Thanks

[roobaron]

Can you post the number of extents for each table and index
and also the explain plan for the query.

Cheers

Paul

[reydp]

try to create huge rollback segment, let's say INITIAL 20M and NEXT 20M.

And force that transaction to be able to use that rollback segment by issuing the command below before the delete statement.

SET TRANSACTION USE ROLLBACK SEGMENT huge_rbs;
DELETE FROM temp_det......blah....blah. ...blah.

[pando]

you have to index

T.orig >= temp_det.orig

[jmodic]

A composite index on ORIG_CAP(DT, ORIG) will solve your problem. Right now, without any index on ORIG_CAP Oracle must perform full table scan on ORIG_CAP for each row deleted. Also a composite index on TEMP_ORDERS(DT, PROD_ID) would speed things a bit.

BTW, neither the number of extents nor the large rollback segment assigment are in any way relevant for the performance of this DML.

[Edited by jmodic on 11-13-2001 at 09:47 AM]

[reydp]

_________________________________________

BTW, neither the number of extents nor the large rollback segment assigment are in any way relevant for the performance of this DML.
_________________________________________
thanks jurij,
I didn't pay attention to the sql statement that tansdot posted, there was an indexing problem to begin with.
For a while I thought it was an INSERT statement.....heheheheh...

cheers.

[reydp]

though delete will still use rollback segments, I believe index should be fix first.