Skip to Content

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

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Aug 19, 2017 at 07:25 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 21, 2017 at 12:00 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.