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.

Regards

Gerid
yeraiAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
Analyze the number of the indexes over the DB.
If there are indexes the delete process will be hard.

Workaround:
If there are indexes you can try to invalidate them
and to delete. After that recreate the indexes.
0
 
SujithData ArchitectCommented:
>> 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.
0
 
multithreadingCommented:
Update on a single fixed-width column is faster than delete on an indexed table.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
sdstuberCommented:
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.
0
 
sdstuberCommented:
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?

0
 
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.

regards

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

gerid
0
 
sdstuberCommented:
"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?
0
 
yeraiAuthor Commented:
sdtuber:

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

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

0
 
morphmanConnect With a Mentor Commented:
Ok,

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.

table_1_online
table_1_offline

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.

0
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0
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.

All Courses

From novice to tech pro — start learning today.