KarthikBabu
Programmer
We have quite a unique requirement.
We are running Sybase 12.5.03 on HP 1280 (Compaq Tru 64 5.1B). We have this monster table (name it Table_A) that stores the transaction information of different user accounts. The table is HUGE - stores 450 Million records and has a Clustered Index on User_ID,Transaction_date. The locking schema on the table is 'data pages only'. The cluster ratio of the table looks bad - 35% and the queries are taking long time to process. So it was suggested that we rebuild the clustered index and our DBAs insisted that no users be connected to the system during the rebuilding of the indexes. The rebuild of the index in a similar test environment with similar volume (430M records) took about 13 hours to complete. So we have predicted that it will take about 16-17 hrs in our production environment. Since the users have to stay out of the application, 16-17 hrs outage is not acceptable for our business partners.
Given the context, we are exploring the different options to reduce the duration of the outage.
1. My first and foremost question is Should the application be really down while an index is rebuilt? fyi - The table is read intensive during the day and write intensive during the night.
2. Is there a way to reduce the duration of the Clustered index rebuild time?
fyi - We have tried other options (re-org rebuild). But we ran into a known Sybase bug (7928); as this is a DOL table (data page only locking), doing a re-org exposes the table to a Sybase bug (7928) that could cause data losses. According to Sybase, 12.5.1 fixes the issue, but we dont have plans to upgrade to 12.5.1 until next year.
Any help is greatly appreciated.
Thanks !!
We are running Sybase 12.5.03 on HP 1280 (Compaq Tru 64 5.1B). We have this monster table (name it Table_A) that stores the transaction information of different user accounts. The table is HUGE - stores 450 Million records and has a Clustered Index on User_ID,Transaction_date. The locking schema on the table is 'data pages only'. The cluster ratio of the table looks bad - 35% and the queries are taking long time to process. So it was suggested that we rebuild the clustered index and our DBAs insisted that no users be connected to the system during the rebuilding of the indexes. The rebuild of the index in a similar test environment with similar volume (430M records) took about 13 hours to complete. So we have predicted that it will take about 16-17 hrs in our production environment. Since the users have to stay out of the application, 16-17 hrs outage is not acceptable for our business partners.
Given the context, we are exploring the different options to reduce the duration of the outage.
1. My first and foremost question is Should the application be really down while an index is rebuilt? fyi - The table is read intensive during the day and write intensive during the night.
2. Is there a way to reduce the duration of the Clustered index rebuild time?
fyi - We have tried other options (re-org rebuild). But we ran into a known Sybase bug (7928); as this is a DOL table (data page only locking), doing a re-org exposes the table to a Sybase bug (7928) that could cause data losses. According to Sybase, 12.5.1 fixes the issue, but we dont have plans to upgrade to 12.5.1 until next year.
Any help is greatly appreciated.
Thanks !!