cancel
Showing results for 
Search instead for 
Did you mean: 

how to rebuild all indexes in on command?

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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.




Former Member
0 Kudos

Thanks. I am going to do it on my production server. Just want to confirm it is safe to run it. right?

former_member188958
Active Contributor
0 Kudos


One possible danger is that the script will not have finished running by the end of your maintenance window.  REORG REBUILD will hold an exclusive table lock until it completes.

Answers (2)

Answers (2)

0 Kudos

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

Former Member
0 Kudos

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 ?

0 Kudos

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

Former Member
0 Kudos

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

jgleichmann
Active Contributor
0 Kudos

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

0 Kudos

Hello Jens,

they are using ASE 12.5.x and don't have any SAP Application in use.

With kind regards

Stefan