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!!
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.