Pass-through deletion query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running a series of SQL pass-through queries that delete records from
several tables in an Oracle database. Several of these tables contain 2-5
million records, and Access seems to pause while Oracle processes these
queries. I would like to initiate these queries, and not experience the
delays associated with these delete operations. Is there a way to accomplish
this?
 
Hi, Dale.
I would like to initiate these queries, and not experience the
delays associated with these delete operations. Is there a way to accomplish
this?
Click to expand...

If these tables are "temp" tables that merely hold records temporarily until
the next batch needs to be processed, then truncating the tables would be
more efficient than deleting the individual records. If there are
referential integrity constraints on these tables, disable the constraints,
then truncate the table, then enable the constraints again. It takes time to
disable and reenable constraints, but it's going to be faster than deleting
all of the records.

However, unlike SQL Server, truncating a table in Oracle is not reversible
with a ROLLBACK command, because it's treated as a DDL statement which always
ends with an implicit COMMIT. So make sure you truly don't need these
records before truncating them.

Your Oracle DBA can grant you permissions that would make this process even
faster, but in general it's unwise to grant those permissions to regular
users. If truncating the tables isn't fast enough, talk to your Oracle DBA
and he'll decide whether your situation is one of the rare exceptions.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
Gunny,

Thanks for the input. Unfortunately, I'm only deleting some of the records
from each table, so your suggestion does not apply.

Dale
 
Hi, Dale.

If you are only keeping a small subset of the total records, then you may
want to archive those records, truncate the table, then copy those records
back into the table. If you have a large number of records that you are
keeping when the others are deleted, then this suggestion won't help.

Presumably, you have an Oracle DBA instead of someone sitting in for him
till your organization can hire one. If that's the case, then your tables
are already indexed and partitioned appropriately, and have separate
tablespaces on separate disks managing indexes and data in parallel, and
efficient management of extents for storage, et cetera. You've got as much
speed as you can hope for with your hardware and network. If your
organization doesn't have an Oracle DBA to do performance tuning, then put
one on your Christmas wish list and plan for a coffee break each time you
need to delete all those records.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as
"Answers," so that all may benefit by filtering on "Answered questions" and
quickly finding the right answers to similar questions. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top