As I earlier promised, I had to come back on the index creation and transportation in SAP landscape…especially for big SAP tables.
As I already wrote, this is something a must know and practice knowledge for both the ABAP and the BASIS. When an index required to create and then transported to the Production system within a landscape.
Let as discuss this scenario in more detail, keeping in mind when the database behind the SAP landscape is Oracle. When an index is created in Oracle using plain Create Index command, it locks the underlying table (do not allow DML operation unless creation is complete) and when SE11 is used for creating index, it uses simple create index command. The time taken is directly proportional to the no of records in the table for which the index is created. So the more the size is the more is the locking period. One needs to judge the time of importing of the transport having an index as one of its object. Although it seems quite simple statement – in actual scenario it does not happen so.
Transport request landing up in the desk of Basis Support was never examined for its content. As basis normally do not understand it. ABAP and Functional do not think it essential to provide summarized information about the transport contents for Basis to understand. So Basis right away start to import the transport to production and it locks the underlying table let say BSIS and the entire operation using BSIS is stuck. Ultimately this is a service outage (although some smart service managers will still dare to call it a partial outage).
To tackle such situation Oracle has an option of creation of index online and in parallel (use if your production system has multiple CPUs/Cores – nowadays are very normal). SAP also recommends to create the index first using database utility only (at least SQL*Plus in case of oracle) and then transport from development so that only the header comes and there is no service outage (refer to SAP Note 334224). There is an option of by passing the redolog option by using no log, but I am seriously against it. Besides if you use parallel option then the improvement using nolog option is negligible.