Reorganize Tables And Rebuild Indexes, The Oil Change For Your Database

Imagine what would happen to your car, if you never changed the oil. At the beginning your car would work great!
As you put the kilometers in, the oil gets dirtier and dirtier, it will stop lubricating and the engine would wear and eventually fail.
If you keep postponing the oil change, it is more likely things will break, and your repair costs will add up quickly to a more expensive maintenance.

Database maintenance (reorganizing tables and/or rebuilding indexes), same as the car maintenance, if applied regularly makes your database and application run smoothly.

To better understand what database maintenance is, and why is it needed, today’s post will answer the following questions on the topic:

1. What is Database Maintenance and Why Should I Care?
2. Why And When Do I Need To Rebuild Indexes and Reorganize Tables?
3. Six Things To Consider When Reorganizing Tables and Rebuilding Indexes.
4. Creating And Testing The Deployment Plan.
5. Three Critical Things That DBAs (Could) Forget During Database Maintenance.

1. What is Database Maintenance and Why Should I Care?

Think of reorganizing tables and rebuilding indexes in the database, as the oil change for your car. At the beginning (when database is released to the users), everything is working great! Performance of the application is good, everyone is happy.
As the transactions happen every day (you put kilometers in your car), and the users are running insert/update/deletes against the tables, your database is in need of an “oil change” so to speak, basically maintenance on the tables and/or indexes.

Postponing the database maintenance, could result in things breaking, SQL queries running slower, higher resource usage for the database and server.
Postponing the database maintenance, could result in bigger outages and more effort to perform the maintenance work.

2. Why And When Do I Need To Rebuild Indexes and Reorganize Tables?

As users are accessing the application and data gets inserted, updated, deleted, tables grow in size, the high watermark grows as well. Indexes get bigger and could get fragmented.
Reorganizing tables and rebuilding indexes don’t always have to happen at the same time.
Reorganizing tables, invalidates the indexes on the table, thus the index rebuild is necessary. However, rebuilding index can be performed online, independent of reorganizing tables.

When do I perform the full task of reorganizing the tables and rebuilding indexes?

• when a massive amount of data is deleted, and I am not expecting to have this amount of data inserted any time soon.
• when a table partition has most of it’s data deleted. In a perfect world, the whole point of having partitions, is to be able to drop partitions when needed, instead of deleting data from the partition. This is not always possible, sometime old data needs to be retained for legal purposes (and not only). It is not up to the DBA what data gets deleted, we don’t decide the business logic.
• when I need to reclaim space from a tablespace. Recently I went through a process of partitioning a large table (250GB). During the process, the new partitioned table was created in a new tablespace. After the partitioning process, the “old” tablespace, where the table resided, had 250GB of free space, that I could not claim back, unless I moved out all the tables from that tablespace.
• when there is a performance problem and I know reorganizing the table and rebuilding the index helps.

Why do I need to reorganize tables and rebuild indexes?

• to reclaim space in the database, and depending on the scope of the reorg, at the OS level as well
• to prevent performance problems
• to fix performance problems

3. Six Things To Consider When Reorganizing Tables and Rebuilding Indexes.

a. Define the scope of the maintenance process:

Before you start on the process, you need to determine the scope of the maintenance

• one table and all indexes for the table
• multiple tables and indexes
• all tables and indexes in a particular tablespace
• all tables and indexes for a schema
• indexes only

b. Outage requirements:

Reorganizing tables requires an outage for the application, because Oracle needs an exclusive lock on the tables.
During the reorg process, indexes become unusable. Some applications cannot handle this.

Rebuilding ONLY indexes, can happen online, while the application is running.

c. Space requirements:

Extra space is needed during the maintenance process.
Verify you have enough space to hold 2 copies of your table(s) and indexes.
Verify you have enough space to hold the archived logs that get generated during this process.

d. Performance hit on the database and the server (other databases as well):

Reorganizing tables and rebuilding indexes are I/O intensive processes. Even other databases that run on the same server could get affected by this task.

e. Different Table Types, different maintenance approach:

• regular Oracle tables can be reorganized with the standard alter table move command
• IOT tables require a different statement
• tables with LONG data type require export import in order to be reorganized.
• tables with LOB data type require “special treatment” as well.
• materialized views have their own alter mat view commands

f. Automate the process:

Automating this process eliminates human error and speeds up the process.
Create scripts that will generate your alter commands. Create these scripts as dynamic as possible.

4. Creating And Testing The Deployment Plan.

You start in the test environment. As you work through your scripts, you will encounter some errors and challenges. Fix them one by one. Document the fix for future reference.
Create and Finesse your Deployment Plan!
Time is critical. Can the maintenance fit in the outage window? If not, can you break in into two deployments?

Test, Correct, Document and Test Again! (until you have a smooth deployment)

5. Three Critical Things That DBAs (Could) Forget During Database Maintenance.

As DBAs, we are so focused on the task, that sometimes we could forget some critical things.
From my experience these 3 things could be missed very easily, and are very important:

• table/index/tablespace size before and after database maintenance. This is really important. You need to be able to determine how much space was reclaimed/saved by the maintenance process.
• invalid objects before and after database maintenance. This is critical. You need to be able to confirm that the maintenance process did not invalidate any existing objects.
• gathering table and index statistics. This is critical as well. When a table is reorganized, statistics become invalid and must be collected. When the index is rebuilt, statistics are gathered automatically during the rebuild process.

Now that I have set the foundation for the database maintenance process, I can get more technical with statements, scripts, automation, deployment plan…next week!

Follow me next week for all the fun stuff!

-Diana