Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    62

    Cool Unanswered: SYBASE database reorg

    Hello,

    I like to reorg my database and to compress table and indexes. My database is a very big one, so I need to reorg only table which need to be compressed.

    I phone SYBASE support and they say me I have to look at table or index cluster ratio to know if a table or an index need to be reorganized.
    They say to use the optdiag command to know this cluster ratio.

    I test this command and saw that the result is very complex. And it'll be very difficult to extract tables name from log file.

    1) Is there another way to list tables and index to reorg ?
    2) Is there a web site where I could find some administration scripts (my database is on Windows) ?

  2. #2
    Join Date
    Jan 2005
    Posts
    6

    Reorg

    Hi,

    Here is a handy script that I wrote to give you an idea of the state of your tables. Simply run it in the DB in question. If you then order the output, you can see which tables (or indexes) have the most deleted pages, or the greatest ratio of empty pages to pages allocated etc. You can also use this to write a script which generates reorg commands to be run based on smallest tables first... if doing reorg rebuild the database requires at least the size of each table in free space to complete, so by doing the smallest tables first you have then freed up a lot of space before you get to the larger tables.

    I hope this helps... Regards, Carl.

    select tbl.name "Table Name",
    CASE
    WHEN stat.indexheight > 0 THEN idx.name
    ELSE ""
    END "Index Name",
    stat.indexheight "Idx Height",
    stat.leafcnt "Idx Leaf Cnt",
    Convert(Numeric(27,3), stat.leafcnt * 2.0000 / 1024.0000) "Idx Leaf Sz",
    stat.pagecnt "Page Cnt",
    Convert(Numeric(27,3), stat.pagecnt * 2.0000 / 1024.0000) "Sz Pgs",
    stat.emptypgcnt "Empty Pages",
    Convert(Numeric(27,3), stat.emptypgcnt * 2.0000 / 1024.0000) "Sz Empty Pgs",
    CASE
    WHEN stat.pagecnt > 0 THEN Convert(Numeric(23,3), ((Convert(Numeric(23,3), stat.emptypgcnt) / Convert(Numeric(23,3), stat.pagecnt)) * 100.00))
    ELSE 0.00
    END "Ratio EP/P",
    stat.rowcnt "Row Cnt",
    stat.delrowcnt "Deleted Row Cnt",
    stat.forwrowcnt "Forwarded Row Cnt"
    from sysobjects tbl,
    systabstats stat,
    sysindexes idx
    where tbl.type = "U"
    and tbl.id = stat.id
    and idx.indid = stat.indid
    and idx.id = tbl.id
    order by tbl.name, idx.name

  3. #3
    Join Date
    Aug 2003
    Posts
    62
    It will be very helpfull for me.

    Thanks a lot

Posting Permissions

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