Solved

it's faster delete or update

Posted on 2007-12-06
14
861 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
14 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 47

Accepted Solution

by:
schwertner earned 250 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
 
LVL 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

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 73

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 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Stay Alert! 13 62
Out of Sequence numbers for today 25 47
query in Oracle forms Builder 2 27
Extract the first word (before the , ) 2 21
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now