Delete Performance

Posted on 2010-11-17
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
Question by:ajexpert
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +2
LVL 74

Accepted Solution

sdstuber earned 250 total points
ID: 34157341
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.
LVL 74

Expert Comment

ID: 34157359
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.
LVL 14

Author Comment

ID: 34157401
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 34157588
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.
LVL 11

Expert Comment

ID: 34157851
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.
LVL 74

Expert Comment

ID: 34158048

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.  

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.

LVL 48

Expert Comment

ID: 34158220
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.
LVL 14

Author Comment

ID: 34158224

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?

LVL 11

Expert Comment

ID: 34158279
@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.
LVL 14

Author Comment

ID: 34158294
Ok Sean and Akenathon, I accept its my assumption and my mistake :)

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

Expert Comment

ID: 34158350
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
LVL 11

Assisted Solution

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

Read about the options here:

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!
LVL 14

Author Comment

ID: 34158492
awesome link Akenathon

LVL 14

Author Closing Comment

ID: 34158726
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.
LVL 11

Expert Comment

ID: 34159011
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 :-)

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
add more rows to hierarchy 3 46
return value in based on value passed 6 49
Oracle programming for starter 14 74
sql script run from cron does not work 46 29
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

734 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