Building Indexes for the HANA Cloud, Data Lake

I recently wrote a blog on how to determine the proper index usage for the HANA Cloud, Data Lake as well as some sample syntax on how to create indexes.

Index Strategy for SAP HANA Cloud, Data Lake

If you simply took my sample at the end and ran that on your Data Lake system that has any sizable data volume, you would notice that the time to create the indexes can be significant: the more data you have, the longer it takes to index the data.

HANA Cloud, Data Lake and the underlying SAP IQ technology each have the same extended syntax that allows for administrators to create multiple indexes via a single command.

In the other blog, I used this SQL as a sample of how to create Data Lake based indexes:

call "SYSRDL#CG".REMOTE_EXECUTE('
    create HG index table_a_date_field_HG on table_a ( date_field );
    create HG index table_b_date_field_HG on table_b ( date_field );
    create DTTM index table_a_date_field_DTTM on table_a ( date_field );
    create DTTM index table_b_date_field_DTTM on table_b ( date_field );
');

The main issue with this syntax is that it will force each index to be created serially, one after the other.  On a small table, this is not an issue.  On table or a series of tables, though, this can delay the time to get indexes into the system.  HANA Cloud, Data Lake and the underlying SAP IQ technology have a SQL extension that allows for all indexes to be created simultaneously and in parallel, all from within a single SQL statement.

With a small change, the previous SQL can be changed:

call "SYSRDL#CG".REMOTE_EXECUTE('
begin parallel iq
    create HG index table_a_date_field_HG on table_a ( date_field );
    create HG index table_b_date_field_HG on table_b ( date_field );
    create DTTM index table_a_date_field_DTTM on table_a ( date_field );
    create DTTM index table_b_date_field_DTTM on table_b ( date_field );
end parallel iq;
');

The additional syntax is simply “begin parallel iq”, followed by the create index statements (with semicolons after each command, followed by “end parallel iq;”.

Enjoy!