Difference between reorg-rebuild and index re-create in Sybase ASE

[SybDBA]

Hi All,

Could someone please explain what the main differences are between reorg-rebuilds and index re-creation in Sybase ASE?

SAP Sybase Forums - ASE - Administration - Difference between 'reorg rebuild index' and drop and rec...

(not answered )

Regards,

Pankaj

[javier_barthe]

Hi,

As Mark explain I think that one of the most important points between them is performance impact cause by a missing index on a prod env. Reorg rebuild the index may generate some minimal locks over user workload but if you execute it over a low workload you may not have any problem.

There are also another points depends on what type of index (PK/clustered) you are rebuilding or recreating. PK gets worse if recreate process its choosen because of integrity constraints defined and clustered index also because of nonclustered index rebuild at the same transaction if not dropped before.

Hope helps.

Regards.

Javier.

[former_member89972]

In the latest ASE versions it boils down to availability of table and index(es) when a task is run. 

Re-org allows you to pick and choose scope (tabesl, index(s) etc), your time windows and allows access to data and indexes as the exist during the operation.

Drop and re-create of index (especially on huge tables) will affect queries that depend on the indexes until they are available again. There is also a requirement of building clustered index first.

Minor differences on statistics updates.  Going by the DOCs Re-org rebuild on index will update stats only if sufficinet changes are noted.  Create index will re-buid the stats again.

HTH

Avinash

[kevin_sherlock]

I understood the question as only pertaining to "reorg rebuild".  Rebuild does not allow time and restart capabilities.  And both "reorg" and "create index .." allow "online" option for availability to base table during the operation.

And even though the docs mentions some weird reference to statistics as you mention, I'm not sure I totally buy into that as it's stated in the documentation.

[kevin_sherlock]

Well, speaking in terms of recent versions of ASE (15.7, 16.x, etc):

It depends on what you mean by "drop and recreate index".  Keep in mind that reorg will recreate your index with it's previously defined storage managment properties such as "fillfactor", "max rows per page", "reserve page gap", segment, compression levels, etc.

If you just simply execute "create index <indexname> on <tablename>(<colspec>)", well then, you are subject to the default server level settings for space management, etc.

Although you posted a reference to a very old version (12.5.4), I can't say whether there was or wasn't some kind of bug involved there.  But, without the complete picture in terms of the properties of the index, the EXACT commands that were run, etc. it's hard to tell what was going on there.