delete operation in Oracle

[whistler]

I do frequent deletion of the contents of an Oracle table that I link to from within Access 2003 through ODBC.

The table usually contains about 1000 records and it takes anything up to about 5 minutes.

Are there any considerations (or maybe even code snippings that I could use) to optimise the performance.

I now create a dao recordset rst that I loop through, something like

dim rst as DAO.recordset
set rst = currentdb.openrecordset ("TblCustomerIDs")
while not rst.eof
rst.delete
wend

Any suggestions ?

Thanks in advance ! Jos.


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[John W. Vinson]

I do frequent deletion of the contents of an Oracle table that I link to from within Access 2003 through ODBC.

The table usually contains about 1000 records and it takes anything up to about 5 minutes.

Are there any considerations (or maybe even code snippings that I could use) to optimise the performance.

I now create a dao recordset rst that I loop through, something like

dim rst as DAO.recordset
set rst = currentdb.openrecordset ("TblCustomerIDs")
while not rst.eof
rst.delete
wend

Any suggestions ?

I'd create a Passthrough query (in ORACLE SQL syntax) and execute it. That way
Oracle will do all the heavy lifting. I haven't used Oracle in ages, so I
don't remember if it uses TRUNCATE tablename; or just DELETE * FROM tablename;
but either will be a lot faster than a recordset.

<checking> Yep; TRUNCATE tablename; will work. It's a tactical nuclear device
though - deletes all records, no log, no rollback, and no mercy. Use with
care!

John W. Vinson [MVP]