Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    3

    Unanswered: Rebuilding indexes in Sybase 11 vs 12

    Greetings.
    We're preparing an upgrade of an application and will also be updating from a Sybase 11 server to a Sybase 12 server.
    Using the exact same stored procedure that works in Sybase 11 to rebuild the 9 indexes on one table, it appears that the indexes are rebuilt but in a different order. When a user attempts to login to the app, they are unable to because the indexes are out of an expected order.
    I've manually dropped & recreated the indexes and the user could then get in. But when I again ran the stored procedure to rebuild them, the order of the indexes is again different. It's also in a different order than when the stored procedure was run the night before.
    Assuming that this rebuild order is a Sybase issue, is there a way that I can dictate the order?
    Thank you for any input!!

    John

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya jarmstrong,

    I don't think the order of the index really matters. The order is determined by indid, with Clustered index always with indid=1. The rest of the index's get indid based on the sequence of creation.

    Then again, the order of creation has nothing to do with what index gets picked by the optmizer. It seimply looks at the statistics available at that time in systabstats and chooses a particular index over another.

    What you really need to do is run UPDATE STATISTICS, that should solve your problem. The reason being, you are on 12 optmizer and you may be using statistics data from 11.0.3.

    Cheers
    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
  •