Hana Spatial - drop or ignore spatial index during data load

[hannu_huttunen43]

Hi,

It is possible to drop and recreate (or disable/enable) spatial indexing in Hana? I have not found such commands from anywhere.

That is, we have to import a lot of spatial data into SAP Hana, and I think it would be much quicker if we could load data into the table that has no spatial index. And after the data load is completed we could create the spatial index.

The spatial column in our table is something like this

CREATE COLUMN TABLE TABLE_A (GEOLOC ST_GEOMETRY(3067) CS_GEOMETRY INTERNAL LAYOUT PLAIN SPATIAL INDEX PREFERENCE DEFAULT VALIDATION NONE BOUNDARY CHECK OFF)

[stefan_uhrig · Accepted solution]

Hi Hannu,

It is not possible to disable the spatial index.

However, the spatial index is not built when you insert a new geometry, but only when the newly inserted geometries are consolidated from the so-called delta part into the so-called main part, which happens in the background (or when the first query with a spatial filter is executed). Hence, the spatial index won't have an influence on insert performance.

If you can control the data insert logic, you can accelerate data inserts by

• disabling auto commit
• using batch inserts (usually has the greatest effect)
• doing parallel inserts (makes only sense if you are not already using the full network bandwidth)

Best regards,

Stefan

[hannu_huttunen43]

Stefan,

" ... but only when the newly inserted geometries are consolidated from the so-called delta part into the so-called main part ..."

Apparently we have done correct moves, because we discovered that disabling auto merge before inserting the data accelerated the job a lot. And the reason why it accelerated is the spatial index.

Thanks !!

Hannu