Heavy delete and insert
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Heavy delete and insert

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    I have noticed that I have a couple of tables in our database which experienced massive inserting and deleting on a daily basis.

    For example we have a table calls events_log I run this quest on that :

    select segment_name Table_Name, num_rows Number_of_Rows , sum(bytes)/1024 Size_in_KB
    from user_segments, user_tables
    where table_name = segment_name and
    segment_type = 'TABLE' and
    table_name = 'EVENTS_LOG'
    group by segment_name, num_rows


    It shows:

    TABLE_NAME NUMBER_OF_ROWS SIZE_IN_KB
    EVENTS_LOG 13028 166138


    I think HWM is too high. Then I try Exprt table , import it again ==> Size didn't change and unused space didn't restore!!!!

    Then I try this :

    create table events_log2 as select * from events_log;
    truncate table events_log;
    insert into events_log select * from events_log3;
    analyze table events_log compute statistics;

    Then it works , result of above query this time is :

    TABLE_NAME NUMBER_OF_ROWS SIZE_IN_KB
    EVENTS_LOG4 13445 10240


    Questions:

    1- I read that imp/exp must restore space for HWM high. Why it didn't do that ?
    2- Does my trucate / insert solution fine? Do you suggest that I schedule it to run on table periodically?
    3- What's your approach to this case?

    Thanks
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    The export/import will do the job if you (a) use the compress=y option and drop the table before the import, or (b) truncate the table before the import. Either way will cause all the extents to be removed and created as needed.

    The truncate/insert solution is fine. You might want to parallelise it to make it quicker. It's easier to automate this since you can do it all from within PL/SQL.

    If the number of rows is fairly constant over time I don't worry to much. The empty extents will be reused as they are needed. If you've got lots of full table scans you will be reading lots of empty blocks up to the high water mark when the table is less empty.

    You have to weigh up the cost of the extra blocks being read against the downtime of recreating the table. This will give you an indication of how regularly you can or should recreate the table.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131

    Question

    Won't the indexes get heavily fragmented if he does not truncate or drop and re-create?

    That is a concern for sure.

    MH
    I remember when this place was cool.

  4. #4
    Join Date
    Nov 2000
    Posts
    416
    So I understand that HWM comes to play onlt in FTS time.
    How can I paralleized the truncate/insert solution?
    Does Hanky comment is important. Do I need to rebuild all indexes for these tables everytime?

    An ounce of prevention is worth a pound of cure

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width