• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 871
  • Last Modified:

it's faster delete or update

i have a process that read select * from table and after delete this records every 30 seconds,

my question is beter practice to do update every 30 seconds and every 1 hour or 1 day delete all record marked in each select, because this process it's very hard in this moment in the DB.


2 Solutions
>> select * from table and after delete this records every 30 seconds
What exactly is being done here?

If you are sure that you dont need the data any more; then truncate the table instead of delete.
Know that:
truncate is :
-- faster
-- doesnt generate rollback
-- and you CANNOT rollback the transaction.
Update on a single fixed-width column is faster than delete on an indexed table.
Analyze the number of the indexes over the DB.
If there are indexes the delete process will be hard.

If there are indexes you can try to invalidate them
and to delete. After that recreate the indexes.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

You asked this question earlier,  same suggestion applies here and with the same questions from me.

Are you processing the records when you read them?  Or are you reading them only for the purposes of deleting them?

Also note, that if you leave the data in the table and merely update it,  that means you have more data to sift through  for all of your other processes too.  Which may mean recreating all of your existing indexes (if any) to include your delete-flag as a way to filter out the data you would have deleted.
Like last time,  if you're not doing any processing of the records, then update or delete as a single statement.

Something else to consider...  How and why is the table getting loaded?  Would it be feasible to declare the table a global temporary and load and processes the data for whatever purposes it has and then simply let the data disappear when the session/transaction ends?

yeraiAuthor Commented:
i can't temporary table because the program that insert in this table it's not the same than read and delete records for this table.


Gerid Garcia
yeraiAuthor Commented:
i can't do it truncate because if this moment than you read other process can do insert
yeraiAuthor Commented:
when i read for the select take information and lnmediatly i shoud delete the same records

"take information"  you mean you do something with it?

Or you read records and then delete them?

If the latter, don't read and delete.  Just delete.
If the former, what's your code look like?
yeraiAuthor Commented:

i have a client in java where your purpose is read the records from any table take information and inmediatly delete the records read, i can't truncate because o cuold occur than in this time other user can insert in the same table

i wait than you uderstand my problem

what do you mean by "take information"

are you doing something with the data after you read it?

does your code looke something like this...

select * from table
loop through above data
     delete from table where column = loop data
end loop

or does your code look like this...

select * from table
loop through above data
     1 - process the loop data  somehow
     2 - delete from table where column = loop data
end loop

or maybe something like this...

select * from table
loop through above data
     process the loop data somehow
end loop
delete from table

I don't understand what you mean when you say "take information"
please provide some code or psuedo-code that demonstrates what you are doing


From the comments in this question, I am making a assumptin that you want to:-

let people write to a table allt he time.

At certain intervals, take the information that is int he table and process it, but still et users write new entries to the table.

There is a construct in Oracle called "partition switching" or sometimes it is called "patririon exhanging", whih i perfect for this.

Basically, you create 2 tables.


people are writing to table_1_online.

you perform  partition exchange which basically points the offline table (empty) to the online table(full) and vice versa instantly.

Peple stillwrite to table_1_online, but now they are writing to an empty table, while you have just exchanged the contents of tabl1_1_online into table_1_offline.

Now you can proess the table_1_offline, clear it out using truncate, and do the same again.

This may or may not apply to yur situation, but we have used it many time with fantastic success. Mak sure you dont need any indexes on the table and it will work 100% of the time.

Forced accept.

Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now