on 10-20-2014 3:36 PM
I want to rebuild all indexes in a database.
If I use following command:
reorg rebuild mytab
go
it will only rebuild all indexes on this table mytab.
Then I need to write a scrip/sp to loop all tables in a db.
Not sure if sybase ase 12.5 has one command to rebuild all indexes for a database.
We do this each weekend ..
There's a few ways to do it .. here's 2...
1) select "reorg rebuild " + name from sysobjects where type = "U"
and run the output
2) declare a cursor reorg_cur cursor for select name from sysobjects where type = "U"
then open / fetch ... and run reorg rebuild on the table name.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
I'm still wondering why you want to rebuild all indexes. Why did you decide to do so ? Do you have frequently problems with the index access ? Did you changed something the sort order or similar ?
With kind regards
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
We reorg or rebuild our indexes ever weekend but not on all tables - just those which slow a bit of fragmentation.
Reorging reduces the fragmentation of volatile tables, reduces space usage (which if you've got slow disks can be a major performance hit) and reduces volatile query plans. I'm also in the process of moving from running "update statistics" to "update all statistics" to further reduced bad query plans.
I don't think this is unusual, every place I've worked in the last 25 years does this.
Are there any places (with large data volumes or just a few tables) which don't ?
Hello Mike,
yes in this case this makes sense, in this care reorg rebuild is the best approach to do so. Depends what version of ASE you use, you can adjust the reorg with different parameters. Also some customers use datachange() to identify the tables that might need a reorg or update stats. Or MDA tables will help you to see how many forwared rows / wasted space you have.
I was wondering cause Kent said he would like to rebuild all indexes in his database.
Regards
Stefan
datachange() is ok but not really good enough to rely on its own. I agree MDA tables help as well.
But generally best to go for overkill and reorg/index rebuild as much as possible - if you've got a 24 hour window for maintenance - then do as much as you can in those 24 hours.
Personally I'd like something built into the server to do this for you. Since every location has a version of this might as well have it built it - and be a good feature to the more novice of DBA's
Hi,
can you tell us the background why you want to this?
May be you also can tell us for which release/application you need this.
If you just want to reorg your system, it is my be more effective to export/import it via R3load.
Regards,
Jens
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.