The most efficient way to create a secondary index on HANA DB?

[Siarljp]

Hi SAP HANA DB Gurus,

we are about to upgrade our ECC system from an Oracle DB to a SAP HANA DB.

This is a question about the creation of secondary indices on a DB table. Previously on Oracle we have learned that the fastest and most efficient way to create secondary indices was to run the SQL statement directly on the DB, and then import the transport containing the SE11 dictionary definition.

We are wondering if the same applies for the SAP HANA DB?

[lbreddemann]

I'm not too sure about your reasoning here.

Why do you say that it's more efficient to manually create the indexes on Oracle instead per transport? Is that about using the ONLINE option for CREATE INDEX or the PARALLEL clause? I

[Siarljp]

We were advised this by an SAP Oracle DB expert, and our own findings - were that for an index that took 1 hour to create on import via transport(which is a really big deal if you have business downtime when your transports are imported), could be created in under 5 minutes when the CREATE INDEX SQL was issued directly at the DB level, and yes I think the parallel clause was used by our DBA. Since discovering this we setup a process for managing all of our Oracle index creations by creating them directly on the DB first in this way.

[lbreddemann]

On HANA most activities, creating indexes included, are automatically executed in parallel without the need for extra command options or unexpected side-effects that need to be cleaned up later (like the parallel degree storage option that sticks with the created index in Oracle and that can negatively influence optimizer decisions or the reduced storage efficiency of parallel built indexes).

[Siarljp]

I have looked into this a bit more - and I think our DB team uses the parallel clause in the Oracle SQL, but that this is not coming from the SQL generated by SE11, would a similar issue arise with HANA?

[lbreddemann]

As mentioned above, SAP HANA handles the degree of parallelity of operations automatically and for most commands there are not even such parameters as PARALLEL. So, no, with SAP HANA you do not have that kind of issue.

[pfefferf]

On a HANA system nearly all of the Oracle relevant indices are not necessary. On HANA performance improvements are possible with indices on single fields which are very selective. For that program exists which do the job: SHDB_INDEX_ANALYZE to analyze which indices would be helpfult, SHDB_INDEX_CREATE which creates the indices and SHDB_INDEX_CHECK to check the created indices. Details can be found in SAP note 1794297.

If you wanna create a singe index w/o the program, you can do that by the standard way using SE11. From a performance point of view it makes no difference to your approach, because the SE11 database tool only triggers the SQL command too (beside the ABAP Dictionary relevant points).

Regards,
Florian