Solved

Delete Performance

Posted on 2010-11-17
15
525 Views
Last Modified: 2013-12-18
A table has 1 trillion records.  

A process deletes all records retaining 100 records.  Stats are gathered on table.  

When we say select * from table will it returns rows faster?  If no, how can we make it faster?

What is the fastest way of deleting records in the above scenario?

Conversely, If we have to delete only 100 records and retain remaining records how can it be done in efficient manner
0
Comment
Question by:ajexpert
  • 5
  • 4
  • 4
  • +2
15 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
select * from table  won't necessarily run faster.
You'll do a full table scan and that scan will have to read all blocks of the table.
Even if all 100 rows are in the first couple of blocks, Oracle won't know that until it scans all of the blocks.
If you read 100 rows at a time though, you could have a quick perceived scan because you will have stopped the scan from going further than really necessary

faster would be to copy the 100 rows you need, then truncate the table and put the the 100 rows back.

Deleting 100 rows out of a trillion won't significantly affect your stats.  Hopefully you have an index to help you find the rows you want to delete.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
If you don't have an index,  it will probably be faster to do the full table scan rather than create an index and then use that to do the delete.  Provided this is a one-time effort.

If you'll be doing the 100 row delete frequently, then you'll probably want an index.
0
 
LVL 14

Author Comment

by:ajexpert
Comment Utility
Thanks Sean,
So in 1st scenario, (Assuming we have index), will it do full table scan even after stats are up to date?  This table now has only 100 records and may reside in couple of blocks.

If we have to copy 100 rows means we are creating another table.

Though I do agree with your comment of copying the rows, I am trying to understand why the delete will be slower even if stats are up to date
0
 
LVL 3

Expert Comment

by:mpaladugu
Comment Utility
If you are performing delete, oracle will write the deleted records into rollback segments, that will give uou  an option to rollback after the delete and before commit.

if you truncate the table, oracle will not write any rollback segments, and you cannot rollback if you truncate once.

i think the fist option of truncating and inserting 100 records is the better option.
0
 
LVL 11

Expert Comment

by:Akenathon
Comment Utility
This assumption is not correct: "This table now has only 100 records and may reside in couple of blocks.". Oracle will NOT "defragment" your table just because you delete most of its rows, nor because you gather statistics on it. It will still be e.g. a 1GB table with only 1KB worth of data, contained in 100 rows scattered wherever they happen to be phisically placed (which you cannot control for the most part).

Even if the 100 rows happened to be located in the first two blocks of the table, when Oracle does a full table scan it has to go through all the blocks, because it doesn't know whether they are empty or not.

One easy way to reclaim that space in your table (and all its indexes), is to:

create table temporary_storage as select * from your_table where <something_that_identifies_your_100_rows>;
truncate table your_table;
insert into your_table select * from temporary_storage;
drop table temporary_storage;
execute dbms_stats.gather_table_stats('your_table_schema','your_table',cascade=>true);

Open in new window


Plus, a truncate is executed in a split second... instead, delete must go through every table block plus every index block for every index on the table, mark the data as deleted, generate a ton of undo data, redo for the delete, plus redo for the undo... no wonder it will take much longer.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Akenathon,  

who made the assumption that the 100 rows were in the first few blocks?
I think you're trying to counter an argument that was never made.  :)
In fact, your argument sounds pretty much exactly like what I said.  


ajexpert,
stats don't actually "do" anything for query speed.  They give information to the optimizer on what type of plan to take.   So,  if you have a table with 100 rows and stats that say it has 100 rows  the optimizer will think the table is small and will likely do a full table scan.  But, even if you had a good index, how would "select * from table"  use that index?  What would be the index lookup?  So, good stats, bad stats, no stats,  "select * from table" will still use a full table scan.  And, as I mentioned above and Akenthenon also stated, the scan is of table blocks,  not of table rows.


0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
The most revolutionary and unexpected solution will be to create a new table with the needed 100 record.
After that drop the original table with their one bilion minus 100 records.
Finaly create the original table using that 100 records.
Indexes can help or may not help. It depends on selectivity. In this case the creating of the index will consume significat time.

I 'speculate" with the assumption that dropping a table means to drop all Oracle blocks, but not to walk through the records and of course all dictionary entries on this table.
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.

 
LVL 14

Author Comment

by:ajexpert
Comment Utility
Sean,

I am not trying to counter argue, (so please don't get angry on me ) but you may look at the your comment ID: 34157341 for the assumption.

Its surprizing to know that table size will be 1G even after deleting most of the data and rows will be "scattered" which we cannot control.

With Oracle 11g don't you think Oracle should defragment the data and by some process Oracle should store these 100 records in contiguous data blocks?

In other words are we saying that High Water Mark is never reset during delete and there is no way of reset HVM?

Thanks
0
 
LVL 11

Expert Comment

by:Akenathon
Comment Utility
@sdstuber: The author made the assumption. The quote "This table now has only 100 records and may reside in couple of blocks." is from his first comment ID:34157401. My counter was not directed to anything you said.

I agree that we two tend to present similar arguments, I bet everybody who knows Oracle would also think alike. On my post I intended to clarify to the author that Oracle does not auto-defragment tables (or indexes), something that his quoted phrase seemed to imply. However, I skipped the part of "what if I want to delete 100 rows from a huge table" because I consider your answer to be enough to solve that part of his problem at hand.
0
 
LVL 14

Author Comment

by:ajexpert
Comment Utility
Ok Sean and Akenathon, I accept its my assumption and my mistake :)

So any comments on the further questions I asked in 34158224?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
don't worry, I'm not angry.  That wasn't an assumption,  it was an extreme example.  
I started it with "Even if all 100...."  which I then explained wouldn't matter anyway.    Of course that example "could" happen it's just not likely, but even "IF" it did, you'd still scan all blocks anyway.

"should" 11g move the high water mark on delete?  No, probably not.
Many (most?) deletes are eventually replaced with an insert.
You'll just reallocate the space anyway which consumes resources in the deallocation as well as the reallocation.  So moving the high mark on each delete would just add work while not adding any value most of the time.

You can adjust the high water mark with ALTER TABLE and the SHRINK clause
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 250 total points
Comment Utility
Here are some ways to lower the high watermark, but none of them are automatically spawned after a delete.

Read about the options here: http://sysdba.wordpress.com/2006/04/28/how-to-adjust-the-high-watermark-in-oracle-10g-alter-table-shrink/

In short, if you use ASSM you have an ALTER TABLE your_table SHRINK SPACE. That will move the rows to the "beginning" of the segment and then lower the high watermark. Your indexes will also be adjusted, and the process is online but with some (brief) locking. You also have DBMS_REDEFINITION and ALTER TABLE MOVE + ALTER INDEX REBUILD. Whatever you do, gather your stats afterwards!
0
 
LVL 14

Author Comment

by:ajexpert
Comment Utility
awesome link Akenathon

0
 
LVL 14

Author Closing Comment

by:ajexpert
Comment Utility
Thanks Sean and Akenathon.

I never knew that Oracle never frees up the space on "delete" operation ever after gathering stats.

You guys are awesome.
0
 
LVL 11

Expert Comment

by:Akenathon
Comment Utility
Thanks! One thing to remember: Oracle does not change anything in your segments while it collect stats on them. It just looks at them, and notes down what it sees in a separate place. Then, the CBO (Cost Based Optimizer) uses those stats, but still it won't change your segment. You are the only one who changes it using insert/delete/update :-)
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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

728 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

14 Experts available now in Live!

Get 1:1 Help Now