| Delete or Update, what is faster [message #38405] |
Tue, 16 April 2002 23:35  |
Jens Vogelgesang
Messages: 23
Registered: January 2002
|
Junior Member |
|
|
What is the fastest way??
I've to work with an temporary table. Primary key consists out of two columns.
At the moment this table is filled within a loop and then analysed, because it can contain upto 100.00 entriesd, but often contains < 100 entries.
Within the process rows are deleted form the table.
Now I want to know, which is the fastest way:
- delete where (using primary keys)
- delete where rowid
- update set variable (need to add another column)
Jens
|
|
|
|
| Re: Delete or Update, what is faster [message #38426 is a reply to message #38405] |
Wed, 17 April 2002 09:45  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Simply try the various ways:
set serveroutput on
declare
t number default dbms_utility.get_time;
begin
for i in (select rowid rid, pk from my_tab)
loop
delete my_tab where rowid = i.rid;
end loop;
dbms_output.put_line
( 'Elapsed ' ||
round( (dbms_utility.get_time-t)/100, 2 ) );
end;
/
declare
t number default dbms_utility.get_time;
begin
for i in (select rowid rid, pk from my_tab)
loop
delete my_tab where pk = i.pk;
end loop;
dbms_output.put_line
( 'Elapsed ' ||
round( (dbms_utility.get_time-t)/100, 2 ) );
end;
/
declare
t number default dbms_utility.get_time;
begin
for i in (select rowid rid, pk from my_tab WHERE DELETE_FLAG != 'Y')
loop
update my_tab set delete_flag = y where rowid = i.rid;
end loop;
dbms_output.put_line
( 'Elapsed ' ||
round( (dbms_utility.get_time-t)/100, 2 ) );
end;
/
The code above isn't tested but it's a starting point.
Actual deletes are always more expensive than logical deletes (set a delete flag). Using rowid is marginally faster than rowid. If you use the delete flag, you need to include it in your where clauses that query the tables to avoid getting deleted rows. Because the delete flag is either 'Y' or Null (or maybe 'Y' and 'N') it is very poorly selective, so indexing it would generally be a bad idea - it'll just make updates and physical deletes slower.
Make sure you run the test cases a few times to ensure that table data is cached etc to ensure repeatable results.
Better than doing timing is to run sql_trace.
alter session set timed_statistics=true;
alter session set sql_trace=true;
-- do stuff
alter session set sql_trace=false;
run the trace file through tkprof to format it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
