Results 1 to 5 of 5

Thread: Indexes

  1. #1
    Join Date
    Dec 2003
    Posts
    23

    Unanswered: Indexes

    Hello

    We have two large tables of around 15 million records. After we archive a few million records off of them we have been dropping and recreating the indexes. However, this requires a significant amount of space in the data segment. I'm wondering if there is some way to acheive a similar effect as recreating an index without using the disk overhead. It seems my two other options are 'update index statistics' and 'reorg rebuild index'. Will update statistics be enough to improve performance? Does Reorg Rebuild Index use the same amount of space as recreating the index?

    Thanks in advance for any help you can provide!

  2. #2
    Join Date
    Jan 2005
    Posts
    10
    According to sybase, dropping and re-creating the clustered index, you will need 125% of the table size. Reorg rebuild only requires 100% of the table size, but only works on DOL tables. I did some benchmarking a few years ago and the 'reorg rebuild tablename' was faster if you have multiple indexes on the table.

  3. #3
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Will update statistics be enough to improve performance? Does Reorg Rebuild Index use the same amount of space as recreating the index?
    (We manage servers with many tables over ten million)

    Whether reorg is chosen over update_stats depends on the db/table design and how the inserts/deletes have affected the structure of the table.
    1. Where the clustered index has been carefully chosen, and the insert/deletes are therefore spread evenly across the table, update_stats is quite adequate (faster, no additional workspace requirement for the duration). In such tuned tables, fill_factor and partitions (parallelism provides the best "performance" for tables) are usually implemented. In this case, less than 10% [a few million of 15 million] deletes would also steer me toward update_stats.
    2. Where the performance of the table has not been addressed, or the deletes are not reasonably spread across the table, reorg is a better choice. For untuned tables, 10% deletes can affect performance, so I would tend towards reord rebuild.

    Generally, we do update_stats every night, and reorgs or drop/create index every Sunday.

    Also note, if you drop/create index, you can use the with_sorted_data parameter which is much faster (I do not know if it is faster than reorg rebuild).

    For the longer term, you are best advised to ensure that the clustered index (columns, sequence) is carefully chosen, which allows for less ongoing maintenance (update_sats over reorg or drop/create).
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  4. #4
    Join Date
    Feb 2005
    Location
    Omaha, NE
    Posts
    83
    Why not just run "reorg reclaim" instead? Also, the effect of rebuilding the indexes on the column statistics is not the same as "update index statistics".

    If your concern about performance is because of both unused pages after the delete is run, and stale column level stats, use "reorg reclaim" followed by your choice of "update statistics [table] [index]" or "update index statistics [table] [index]". If your concern about performance is because of ineffecient query plans being generated from stale statistics, just do the update stats part. BTW, for DOL tables, don't forget to do "update statistics [tablename] [tablename]". Just rebuilding indexes won't keep the stats (the table level stats that aren't kept up-to-date automatically) for the heap part of the DOL table current.

  5. #5
    Join Date
    Dec 2003
    Posts
    23
    Thanks for the advice guys.

    DerekA -- I think your signature says it all:

    "Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it."

    I inherited DBA duties for this home grown application recently and I'm still trying to figure out whether there was an attempt to do things correctly or just a 'get it done' mentality. The two tables in question are an anomaly in the database and I'm constantly wondering if there was a better way to do things.

    I currently have 2400MB of free space on the data segment out of 7000MB. Archiving regulary should allow me to keep it at that level and still have the space to recreate the indexes when we need to. I think my biggest concern is the time it takes to archive and rebuild the indexes. The total process can take up to 15 hours and our system needs to be available 6 days/week. So that gives me a tight window to troubleshoot problems on a Saturday before I have to restore from a backup. I'd like to be able to cut about 5 hours out of that process by using some of these alternatives in dealing with the indexes.

    Things I will try in my test environment per everyone's suggestions:

    -with_sorted_data parameter
    -reorg reclaim and update index statistics [table] [index]

    Thanks!!

Posting Permissions

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