Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Brussels
    Posts
    57

    Unanswered: tables and allocated pages

    Hi all,

    We have SPL we run every night.
    We run IDS7.31.UD2 on AIX4.3
    Some SPL's do "delete from table" before loading new data into table.
    I know it should be better to "drop table; create table".
    It seems that "delete from table" drops all data but it certainly not release all allocated pages and worse when loading new data in the table it does not reuse already allocated pages.
    1 table has now npused=137 500 and npdata=1362.
    After unload and reload the table and create index npused=3152.
    The table has 2 columns and 1 primary index. nothing complex.

    Why should IDS not reuse all pages still allocated to a table after a "delete from table" ?
    Can someone help us ?
    Should we do cluster index in the procedure or replace all "delete" by "drop, create" ?
    Yves & Willy

  2. #2
    Join Date
    Jan 2005
    Posts
    30

    Thumbs up update statistics medium

    De lete statement will not delete records immediately. It just marked records for delete. So, the space will not immediately available after delete. Eventually, btree cleaner delete records and reclaim the space.

    You have couple of choice:

    1)Delete records, followed by update statistics medium on that table
    2)Drop and recreate the table
    3)Create cluster index after load data. Basically, it will recreate the table with different partition number.

    Cheers!
    Sanjit Chakraborty

  3. #3
    Join Date
    Apr 2004
    Location
    Brussels
    Posts
    57
    Thanks !
    Yves & Willy

Posting Permissions

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