Rebuilding SAP Indexes

Disk based databases require optimisations that reduce the amount of data that is being read from the disks. Index is one such optimisation. Indexes are sorted and usually created on columns that are include in the WHERE clause of most heavily used sql statements.

When I query tries to fetch a set of records, matching a particular selection criteria, instead of reading the complete table, the query checks a suitable index. The index then provides location of the particular records in the table. Now the data blocks containing those particular records are read.

Indexes are not an all weather ally. Poor selection of columns and too many updates to tables can make indexes do the opposite of their intended function.

Index fragmentation

When an indexed field in a table is changed repeatedly, the data is deleted from the index and re-inserted at a new block in the index. The new entries are always added to the right side of the index tree and therefor deletion is relatively to the left.

When the index elements are deleted and added repeatedly, the left side of the index tree tends to be sparse and the right side keeps growing. Depending on the size of index, there might be thousands of sparse blocks, which are being checked during execution.

Fragmented index results in increased usage of database space and more blocks being read into the buffer. This can be avoided by rebuilding the index.

SAP reports to rebuild Oracle indexes

One can measure index fragmentation using the report RSORATAD or using DB02 --> Detailed Analysis --> Enter Index --> Detailed Analysis --> Analyze Index --> Storage Quality.

If storage quality is less than 50% you may need to reorg the index. If you wish to run an analysis on all the indexes, run the report RSORAISQN.

Check SAP note 970538 for more details on the restriction with using this report. Do not run the report without reading the note.

You can also get an idea on the amount of fragmentation by comparing the size of the table and the index. If the size of the index is larger than that of the table, the index is heavily fragmented.

To rebuild index, you can use one the following methods:
1. brspace -f idrebuild -i <index_list>
2. Using DB02
DB02 --> Detailed Analysis (in the "Tables and Indexes" section) --> Object Name: <index> --> Detailed Analysis --> Alter Index --> Coalesce / Rebuild
3. Using RSORAISQ report
   Call SAP transaction SE38 and execute the report RSORAISQ.
   Input the tablespace name and the index name in Object Selection section.
   Check rebuild option in Function Code section.
   A report will be generated with details on the index.
   Click on the Index Rebuild button.
   You will be prompted to feed the name of the index and tablespace again, enter the details and execute.
4. Rebuild indexes offline using the report RSANAORA
5. Mass rebuild of indexes using the report RSORAISQN




If you find this useful, please consider sharing the post with your friends and colleagues on facebook, twitter, google+, linkedin (or any other medium you like!)

Would you like to read more on indexes? The linked post discusses guidelines to creating an Oracle index based on table size.

Comments

Post a Comment

Popular posts from this blog

OS/DB Migration - CMD. STR, TOC, EXT, R3load, DDLDBS.TPL and more

Image
With increasing need to improve performance or reduce costs or vanishing support for operating systems/databases it becomes inevitable to migrate to a better OS or DB. We feel take a quick look at the primary ingredients of the tools for OS/DB migration handled by R3SETUP. Tools - R3load, R3ldctl and R3szchk R3ldctl creates STRucture files, TemPLate files by reading the technical settings for table/indexes in SAP data dictionary. R3szchk calculates the database size object sizes (EXTent files) for the target system based on the STR files created by R3ldctl. The EXT and STR files can be later split into smaller groups. The most crucial tool R3load is responsible for unloading and loading data from the database. R3load uses the files created by R3ldctl and R3szchk and dumps the data into compressed binary format as .001, .002 .. files. It uses cmd files to achieve the task. If the cmd files are not present, it creates on its own. Files Command files*.cmd  are created i
Read more

Fixing Inconsistent Table - Table activation fails due to inconsistency between DD and DB

If you find a table to be available on the database, but not active in ABAP dictionary, try calling SE14 as DDIC user, enter the table name and choose Table > Reconstruct, Execute. If this method fails follow these steps: Call transaction code SE37 and execute the function module  DD_TABL_ACTM with single test On the next screen, do not change any other field except the following and set them as shown below: MODE: 12 NTAB_PUTSTATE: N TABNAME: <enter the correct table name> Click on Execute The export parameter ACT_RESULT should show 0. To verify if the inconsistency is fixed, call transaction SE14 > Extras > Database Object > Check and  SE14 > Extras > Runtime object > Check.
Read more

301 Redirect Using SAP Web Dispatcher

301 redirects are typically used to redirect accesses that include www to non-www or vice-versa. Example, when you call http://blogger.com, the call is redirected to www.blogger.com. An end-user may prefer to call a URL in any manner. You must ensure that all these calls are redirected to relevant URLs with content. SAP web dispatcher can be configured to send a redirect (301) to the client. To enable redirection add icm/HTTP/redirect_<xx> to the web dispatcher's profile file. The parameter uses the following syntax: icm/HTTP/redirect_<xx> = PREFIX=<URL prefix>[, FROM=<pattern for URL>, FROMPROT=<incoming protocol>, FOR=<pattern for host name:port>,TO=<new URL prefix>, PROT=<protocol>, HOST=<host>, PORT=<port number/name>] Let's say you want to redirect all the HTTP requests on mysource.com on 80 port to mytarget.com on HTTPS 443 port icm/HTTP/redirect_0 = PREFIX=/, FROM=*, FROMPROT=http, FOR=mysource.com:80,
Read more