What is an Index in database?
Before discussing about Index Rebuilding let us first discuss the concept of Index in a database.
In a Relational database management system, an index is a copy of one part of a table.
A database index is a data structure that improves the speed of operations on a database table. We can create Indexes using one or more columns of a database table, providing the basis for both rapid random look ups and efficient access of ordered records.
Some databases extend the power of indexing by allowing indexes to be created on functions or expressions.
We can perform Index rebuild using SQL Command or using BRTools and through application level too.
In this article we will focus on how to perform Index rebuilding using BRtools in SAP.
Why Index Rebuild Necessary:
- As discussed earlier, Oracle indexes speed up the execution of SQL statement execution.If we don’t have one, then full data scan will be performed.
- Over the time,large number of inserts, updates, and deletes occurs on table making index fragmented and unbalances.
- Index rebuilding over frequent interval can reduce disk I/O.
Precautions:
- Monitor the space utilization i.e DB may create multiple archive logs during rebuild activity which can lead to system down situation if ignored.
- Index rebuilding activity generates load on database hence we should perform it only outside business/peak hours.
Procedure:
- Login to oracle database using <orasid> user and start BRtools.
- Goto option –>3: Segment Management –> 2. Rebuild Indexes .
- Next screen will ask all required information to perform Index rebuild. You need to provide
“Rebuild Option” as ‘rebuild’
“Index Owner” as owner name of the index – you can find it using DB02
“Index Names” – name of index for which rebuild it so be performed
We need to Press c to continue, here we can see the option-8 confirmation mode is set to Yes, hence it will ask for the confirmation to proceed on index rebuilding.
Press “c” – continue after verifying the information displayed in command line is correct.
it will open the log file for selected index object –> select proceed (c)
This will perform index rebuild actions and will update the task.
