How can I do reorg rebuild all indexes in all databases? Can some one help me with script?

dearvjkumar
dearvjkumar used Ask the Experts™
on
How can I do reorg rebuild all indexes in all databases? Can some one help me with script?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Just run this query:

select "dbcc reindex (" +name +")" +char(10) +"go" from
sysobjects where type="U"

Copy the results, Paste it in a new query window and then Run it.

Hope this helps

Author

Commented:
Hi rrjegan17,

I need a method or script to run reorg rebuild all tables in all databases.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this helps:

select "reorg rebuild " +name + char(10) +"go" from
sysobjects where type="U"

Copy the results, paste in a new query window and then Run it.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Commented:
rrjegan17:

I still run into this at some sites. It's well worth you reading the manuals on what exactly dbcc reindex does. It does not (despite the name, and despite what you seem to be suggesting) rebuild indexes in the way that you think.

"dbcc reindex checks the integrity of indexes on user tables by running a fast version of dbcc checktable. For details, see dbcc checktable on page 226. dbcc reindex drops and rebuilds the indexes where the sort order used is not consistent with the new sort order. When dbcc reindex discovers the first index-related error, it displays a message, and then rebuilds the inconsistent indexes. The System Administrator or table owner should run dbcc reindex after changing the sort order in Adaptive Server."

ie if the sort order of the index matches the sort order of the server, nothing happens.


dearvjkumar:

Be aware that you will need the SELECT INTO option to run REORG REBUILD, it will exclusively lock each table as it runs, and running it on one table at a time serially like this is will be slow. Definitely not something to run while users are doing anything with the system.

Can I ask why you want to run this or think you need to? Certainly Sybase ASE tables should be defragmented... maybe 1-4 times/year depending on what you do to them with INSERTs, UPDATEs and DELETEs.

You can use the "optdiag" tool (included for free with ASE) to get an estimate of how fragmented the tables are. Let's put it this way - you certainly don't have to run this weekly or even (probably) monthly.

Author

Commented:
Hi, thanks for the info. But due to some requirement, i have to enable monthly index rebuild job in our sybase servers. So i m looking for a method to select all database and tables and do reorg rebuild.

But I couldnt find a way to select all database and tables inside each database to execute reorg rebuild.
I need this method not only for reorg rebuild and for someother checking also i required.

So if some has script / proc to execute something on all database and all tables, please help me.
Keeping Joe's warning in mind.

If you increase the points to 500, I will give you a script (two actually).  If you do not have the execute immediate command (12.5.3 IIRC), you will need a shell (not SQL) script; if you have it, the scripts will be entirely SQL (which is much easier).  Try:
declare @x char(80)
select @x = "exec sp_who"
exec (@x)
Cheers

Author

Commented:
Hi Incisive,

I need to loop thru all the DB and all the tables inside each DB. I am looking for a script with that requirement. Can you able to help me?
Yes, I understood, that.  I have such scripts working.  Not just a script/proc that does the <job> on one db.
Note also, along the lines of Joe's post, you have not specified whether you want:
reorg rebuild table
or
reorg rebuild table index
The first de-fragments the heap table and rebuilds all indices.
The second rebuilds just the index (which defrags the index).  I think you want this one, if you are going to execute this monthly.

Cheers

 
This has not been thought out, or you have some non-technical person giving you orders (which will end in tears for everyone).  I do not do such silly things (as reorg rebuild or update stats) in my existing proc, but I do have a working method which executes at the db_name/table/index level.  You simply supply the command (this is important esp. if you are going to do "someother checking as well").

From the SAG manual:

"The following considerations apply to the rebuild subcommand:
reorg rebuild holds an exclusive table lock for its entire duration. On a large table this may be a significant amount of time. However, reorg rebuild accomplishes everything that dropping and re-creating a clustered index does and takes less time. In addition, reorg rebuild rebuilds the table using all of the table's current space management settings. Dropping and re-creating an index does not use the space management setting for reservepagegap.

In most cases, reorg rebuild requires additional disk space equal to the size of the table it is rebuilding and its indexes.

The following restrictions hold:
The table specified in the command, if any, must use either the datarows or datapages locking scheme.

You must be a System Administrator or the object owner to issue reorg.

You cannot issue reorg within a transaction."
What would you like for the DPL/DRL Placement index (the term Clustered Index when applied to DPL/DRL tables is false and misleading, I refuse to use it) ?

Corrections

(The manuals have deteriorated since 11.9.2, some sections are confusing and appear contradictory)
 
1  Reorg rebuild index does not take out a table lock (reorg rebuild <table> does).

2  Reorg rebuild index does not need select_into set (reorg rebuild <table> does).  That means your log dumps are not suspended.

3  The statement
"However, reorg rebuild accomplishes everything that dropping and re-creating a [placement] clustered index does and takes less time."
is complete nonsense.  In fact, the heap is left unchanged, only the index is rebuilt; drop/create the placement index rewrites and de-fragments the heap.  In my varied experience, it is substantially slower than drop/create placement index (but that does take out a table lock); and it hammers the log one hell of a lot.  The same manual later states:
"Although online index rebuilding is allowed on a placement index, it rebuilds only the index pages. The data pages remain untouched, which means datarows are neither sorted nor rewritten to fresh pages. You can rebuild data pages by dropping a placement index, and then re-creating it."
Note the use of the correct term, Placement.

4  Reorg rebuild index requires very little free space in the db (reorg rebuild <table> requires 125% the table size).

----------

What would you like for the DPL/DRL Placement index, would you like special handling (drop/create instead of reorg rebuild index), or leave as is ?

Cheers
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
dearvjkumar,
   Kindly do it in this manner:

1. Tables with heavily fragmented indexes needs to be rebuild
2. Tables with Less fragmented index should be Reorganized.

Just your option to choose either Index Rebuild or Index Reorganize
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Commented:
dearvjkumar:

Building on all the good comments you've received here so far, I strongly suggest you take a step back and think (and discuss with us) what it is you are trying to achieve before getting distracted by what is the correct shell or SQL syntax to implement a particular approach.

You said "But due to some requirement, i have to enable monthly index rebuild job in our sybase servers."

What is the actual requirement?

You said "I need to loop thru all the DB and all the tables inside each DB."

Is that a technical requirement? Are you forced to do it this way? I agree it is very simple to do it this way, but simple is not the same as "best", "suitable" or "appropriate". Do you actually care whether there are iterative loops that single-thread? Have you timed this? What is your maintenance window in which you propose doing this monthly? How much data is in these databases?

Writing a script to do what you want is very simple. A basic "loop through these databases, and loop through the tables (or other objects) in each of those databases, now perform a command per object" is probably the basis of 90% of all DBA scripts ever written.

... but that doesn't mean it's what you should be doing. I and most of the other high-scoring experts here are generally reluctant to tell you how to do something possibly unwise without knowing more about why you think that's what you need to do.

If you say "I have no control or ability to influence this; I have been told to produce a script to do it and that's all that I am interested in" then I suppose we can point you at such a script or show you the outline of one here. I don't think you've told us your Sybase version (or even which Sybase product it is) though. Can you give us at least that much please?

Author

Commented:
Hi Joe,
Thanks for your detailed advice. Yes, I know the difficulty of enabling reorg / rebuild in PROD system. But I am forced to do it / enable it in a automated way (weekly/monthly). Most of our systems are reporting systems not online systems.

I dont have other choice of enabling it in our environment, thats the reason I am looking for a script to loop thru all database and all tables. This is not only for enabling reorg/rebuild, for other purpose also. (eg. periodical table list / index list, rowcount etc)

Could you help me with the script?

Author

Commented:
My sybase ASE is 12.5.4 and 15.0.2 under windows and linux as well.
I am not sure if you are dealing with Joe exclusively, and ignoring the other posts due to blindness or lack of courtesy.  I have no comment on Joe's post. I have previously provided considerations you should make, I have stated that I have such  scripts, and I am still waiting for you to answer the question:

> What would you like for the DPL/DRL Placement index, would you like special handling: drop/create index; or  reorg rebuild index, same as the rest ?
So that I can include that in the script.

Now you are asking Joe personally if he can help you with the script.  Do you want a script for anyone, or just from Joe ?  Let us know, I am quite happy to step back and let Joe provide the scripts and get the points as well.

Cheers
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012

Commented:
IncisiveOne: Just for the record, what you see is what you get, the visible responses here are the only communications I'm involved in or aware of.

If you have a script ready to roll then by all means you should continue here. I've previously done these sorts of iterative loops outside ASE in shell scripts and the like. Your SQL-only solution is more portable and more appropriate when DBA housekeeping has to span multiple platforms.

Summary: Go for it. :)

Comment: Possibly your question is too obscure or arcane for the needs as stated...? (I don't know; I am speculating.) I think we have seen, though, that the needs here are not extremely sophisticated, so the simpler answer (no special handling, ie. reorg rebuild index for the lot) is probably sufficient. (Again, I speculate.)

dearvjkumar: If IncisiveOne puts a script to you, please ensure you select their answer and give them the points. If this were a 500 point question I would ask for 50, but 50/250 is too large a share for what I feel my contribution here has been.
I don't think so.  

For 250 points, you can provide the "very simple" script in SQL, shell, whatever, and you take the points.  I would have thought that, from the outset, it was clear that there is one server involved, so going out to the shell is a bit silly, unless you have no choice.  From 24910694 onwards, it should have been clear that execute immediate was a key issue.  Since OP is on 12.5.4, there is definitely no need to go out to the shell to do something completely within ASE.  I said I had no comment on your post.

I do not want any points for the clarifications and considerations I have provided, if they are beyond the ambit of the seeker.  I will refrain from speculating on your speculations.  However, there is no point in trying provide elegant, efficient scripts to seekers who will not consider the issues; or answer questions (three outstanding); or read my posts.  My script is very short, but it is not simple.  Since you find scripts of this kind "very simple", that's a second prevailing reason.

You guys cannot even keep up with my requirement to change the points to 500, which I specifically declared (I never suggested I would do it for 250).  From the looks of the above, without speculation, the lack of response from the seeker leads me to believe he is not interested in the 500 point script, from his only response being directed at you, it looks like he is proceeding with yours for 250.  Third reason that you  should "go for it".  If this is less than clear to you, read the thread again (sans the interpretations and speculations).

Cheers
Hi Incisive,
Cool...let us not drag this too much.

>> What would you like for the DPL/DRL Placement index, would you like special handling: drop/create index; or  reorg rebuild index, same as the rest ?

Just reorg rebuild index should be sufficient for me.

Great !  I had stripped my proc, and it was ready to go last week.  Actually it executes any command at the index level (yes, across all dbs).

1  Check my profile and drop me an email with full name and company name, address, etc, I will send the legal doc covering IP to you.  Of course it is free, but the acknowledgement of IP and non-disclosure has to be signed.  We cannot post IP on EE.

2  Request attention and ask the mods to change the points to 500.

Given our times zones, you can have it tomorrow.

Cheers

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial