HANA fastest way to insert into table - direct path insert? (/*+append */ hint)

[former_member229185]

Are there any options for quickly inserting millions of rows into a table? In Oracle the /*+ append */ hint was a method of directly inserting into a table without the overhead of transaction control features.

The data is selected from a very low latency on-premise remote MSSQL source.

• column table - 4 million rows x 19 columns - no constraints - 2m43s
• row table - 4 million rows x 19 columns - no constraints - 3m34s

[lbreddemann · Accepted solution]

HANA doesn't have the append hint as this is completely related to how Oracle primarily stores data (on disk). Even with Oracle DBs this hint is to be used with care as it effectively turns the table into a single session resource. It does however not bypass transaction control - else you wouldn't be able to rollback/commit those statements.

HANA doesn't do that. The insert times you report don't seem to be very fast, so I'd recommend checking what takes time here. Maybe the tables are really large already and performing intra-statement delta merges takes too much time.

Controlling delta merges during loading as well as partitioning the table could be effective measures to improve this. There had been quite a number of blogs and discussions around data loading into HANA here on SCN, so I recommend doing a little research on that.