Index rebuild script

[Tison]

Does anyone have a script that will drop and rebuild indexes on Sybase 11.9 tables (using Unix AIX) ?
OR
Does anyone have a proc that will list only the index and constraints in a table ?

 

[Tim1]

Hiya,

The easiest way to find a list of indexes on a table is

SELECT name FROM sysindexes WHERE OBJECT_ID = 'table_name'

OR

SELECT name FROM sysobjects WHERE type = 'I'

However, why do you want to drop and recreate all your indexes? Unless you are doing huge updates, wouldn't it be simpler to run a DBCC or UPDATE STATISTICS to ensure that your index and data match??

Tim

 

[Tison]

We run dbcc checks weekly but I have found that if we regen the indexes monthly, there are performance gains.

 

[Tim1]

Hiya,

You may (probably will) find the same performance gains using UPDATE STATISTICS, and will find that it runs quicker than dropping and recreating all your indexes every month.

Tim

 

[RajD]

Hi Tison,

Did you get the right thing yet? If not I have the exact thing for you.

I'll generalize the script and post it in a day or two. Keep checking

cu

 

[DougSmith]

Hi, I'm a former Sybase instructor/course developer. (Currently unemployed if you can believe it!) Here are some selected links and a crude way to get the information you need to create a script from the system catalogs -without getting too caught up in writing your own).

Ed Barlow's Site:

http://www.tiac.net/users/sqltech/freeware.htm

David Midomer's Site:

http://www.midsomer.org/

(Home of dbschema.pl -- This will do everything for you. Generates a huge script of your entire server if you need it)


Other good resources:

Michael Peppler's Site (SybPerl):

http://www.mbay.net/~mpeppler/

ISUG Sybase FAQ listing:

http://www.isug.com/Sybase_FAQ/

The hack:

If you're really in a hurry, and you are great with a text editor that can do bulk search and replace on columns, you can use the outputs of sp_help (to get a list of tables) and sp_helpindex (to get text description of the index names and keys) to generate the information you need, then edit the outputs into a script.

Once you have this script, rebuilding is 'easy'. Remember to put the drop index <indexname> command before each create index command as a separate statement, followed by 'go' on a line by itself in the isql script.

The big lesson here is always keep scripts to do a logical rebuild. And keep track of how long each operation is taking.

Tips:

FYI - something to beware of: do not stop and restart your server in the middle of running one of these index build scripts - The rollforward of drop index has to rebuild the original index.

And - of course - beware of the time it takes to drop and rebuild indexes.

If you just rebuild the clustered indexes, the non-clustered index are automatically rebuilt. This is another consideration to take in making your script.

Another tip: Do it when there are no other users on your system. When you drop and rebuild clustered indexes, there will be potential for lock wait scenario's, deadlocking etc. This leads people to do irrational things like trying to restart the server.


Good Luck,

Doug dougsmith94703@yahoo.com

 

[tsenthil]

Hai,
i am senthil, rebuilding your index in Adaptive server
in 11 and above you have rebuild_index command which would match your data with the index leaf pages.. however you DBCC with consistence checker to verify the index. these options are required only for mass updates. you can also
acomplish the same by using update statistics.

Regards,

Senthil