This discussion is archived
6 Replies Latest reply: Oct 30, 2013 7:49 AM by EdStevens RSS

Removing blocks

SrinivasM.P. Newbie
Currently Being Moderated

If I populated table1 & table2 with many rows (10 million or above) and then delete all rows (be sure to make DELETE not TRUNCATE) and make TRUNCATE on TABLE2 then response time will be different even both tables contains no data. For TABLE1 all empty blocks must be read, while TABLE2 contains only 1 block to read.

 

I need to use delete statement with where clause. In this case I cannot user truncate than how can I remove the blocks for table1 to improve the performance?

  • 1. Re: Removing blocks
    Saugat Chatterjee Newbie
    Currently Being Moderated

    please explain a little more,what i get from your post is that you are first deleting data from table2 and then truncating the same table

     

    if you have already deleted the data from table1 then why are you truncating it because

     

    delete all rows=truncate

  • 2. Re: Removing blocks
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    If you DELETE most of the rows (but not all), you can

     

    (a) Rebuild the table with an ALTER TABLE table1 MOVE.  You will need to rebuild indexes on the table with ALTER INDEX indexname REBUILD.

    OR
    (b) Shrink the table with ALTER TABLE table1 SHRINK SPACE ;   In this case you do not need to rebuild indexes.

     

    Is this going to be a regular (e.g. daily) activity that you'd load ten million rows and then delete most of them ?

     

    Hemant K Chitale

  • 3. Re: Removing blocks
    SrinivasM.P. Newbie
    Currently Being Moderated

    Yes!

    I'm deleting more than 1 lakh rows every time and inserting the same

  • 4. Re: Removing blocks
    Aman.... Oracle ACE
    Currently Being Moderated

    SaugatChatterjee wrote:

     

    please explain a little more,what i get from your post is that you are first deleting data from table2 and then truncating the same table

     

    if you have already deleted the data from table1 then why are you truncating it because

     

    delete all rows=truncate

    I am afraid not as delete all rows won't reset the HWM as truncate would do.

     

    Aman....

  • 5. Re: Removing blocks
    TSharma-Oracle Guru
    Currently Being Moderated

    If you are deleting the 100 thousand rows and inserting the same number of rows then why do you need to delete blocks or shrink table. The empty space from delete will be reused by the next insert you will do unless you are doing the insert with 'append' hint.

     

    Also, 1 lakh rows does  not seem alot for Oracle. Still if you want to do then You can use shrink space

     

    Alter table <table_name> shrink space cascade; Here cascade keyword will shrink the index as well.

  • 6. Re: Removing blocks
    EdStevens Guru
    Currently Being Moderated

    How is this discussion really any different from your other, How to know how much I can shrink the table

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points