skip to main content

SAP NetWeaver Newbie

Creating Index in Oracle-based SAP Application

This is a thumb-rule for creating indexes in Oracle:

For small tables (< 100 MB)
The creation of index on such tables usually takes a few minutes. Therefore, when you are creating the index pick a time of day where a lock on the table (transactions will be slow due to lock on the table) for a few minutes is acceptable.

For medium tables (100 MB - 1 GB)
Choose a week-end or a time frame where no user will be running a report that will be using this table.

For large tables (>1 GB)
The index creation on these tables will be very time consuming and you may have to do this as a downtime. You will have to use DB specific options to speed up index creation. For example in Oracle, you can use PARALLEL DEGREE while creating it.
a. Create the index in the DEV system using SE11.
b. Take note of the exact name of the index.
c. Create the index at Oracle level in QA with the same name that was used in DEV using the following syntax:

create index <index name> on <table name> <field1, field2, ... fieldn>
nologging tablespace <tablespace name>
parallel (degree <number>)
pctfree 1
storage <storage clause>
online;

d. Transport the index to QA. As the index already exists, it won't be recreated. The transport will only move the ABAP dictionary information.
e. Remove reset the parallel degree to 1
alter index <index name> noparallel logging;
f. Repeat c, d and e in production.

If you are looking to reorganize an index, you must read this: http://www.sapnwnewbie.com/2011/12/rebuilding-sap-indexes.html

1 comment:

  1. The Best SAP Training Video You Ever Seen...
    Visit WWW.SAPVISUAL.COM


    We Guarantee...!!! You CAN be a certified SAP consultant fast...

    ReplyDelete

Email Subscription

Get every new post into your inbox by subscribing us.

Want a reason to subscribe?
1. This sitemap might convince you to subscribe.
2. We do not misuse email IDs. We respect privacy.

© 2008 - 2017 sapnwnewbie. All rights reserved.