?
Solved

it's faster delete or update

Posted on 2007-12-06
14
Medium Priority
?
870 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:yerai
13 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 20419272
>> 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
 
LVL 7

Expert Comment

by:multithreading
ID: 20419386
Update on a single fixed-width column is faster than delete on an indexed table.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 1000 total points
ID: 20419408
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 74

Expert Comment

by:sdstuber
ID: 20419478
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 20419502
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
 

Author Comment

by:yerai
ID: 20419670
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
 

Author Comment

by:yerai
ID: 20419696
i can't do it truncate because if this moment than you read other process can do insert
0
 

Author Comment

by:yerai
ID: 20421289
when i read for the select take information and lnmediatly i shoud delete the same records

gerid
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20421324
"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
 

Author Comment

by:yerai
ID: 20421524
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 20421615
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
 
LVL 6

Assisted Solution

by:morphman
morphman earned 1000 total points
ID: 20437648
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20936940
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question