How to create index for Column Table in SAP Hana?

[Former Member]

I have a little problem in create a index for a column table in sap hana. I need to create index and the value maybe could be null.

In Oracle SQL I create like this:

CREATE INDEX IDX_MY_INDEX ON MY_TABLE (NVL(MY_COLUMN,' ')); 

But in the Sap Hana, I don't know what can replace the nvl function.

Can you help me?

Message was edited by: Tom Flanagan

[lbreddemann · Accepted solution]

Sorry, but are you sure that you know what you are doing here?

For Oracle, this approach of a function based index could be useful if you want to be able to use the index for rows that contain NULL in column my_column.

For heap tables and btree indexes those values won't be contained in the index in Oracle.

You can of course also create a calculated column in SAP HANA where NULL is replaced, but why would you? SAP HANA's column store works very differently and NULL values are indexed like any other value.

The other and more important question is: why do you think you need to index your data set at all?

- Lars

[Former Member · Accepted solution]

Sorry, but I'm newbie in Hana.

Well, you tell me, I'll have no problems if the field of my table, to be used in the index is null?

Oracle have this problem, if the column is null fields.

[lbreddemann · Accepted solution]

The topic here is actually manyfold:

1.) you index a column that can be potentially NULL. And since you take special action to ensure that these NULLs are actually indexed (and you need to do the same when querying the data... do you do that?) I assume that it's important for you to select the NULL values specifically...

That leads to the conclusion that the NULL values are actually of importance to your application - which means your data design likely is flawed (except of course you implemented rigid three-valued-logic).

2.) in SAP HANA column store, secondary indexes are far less often required than in Oracle (or other row store oriented systems for that matter). So, as a starting point I would recommend *not* to create any index at all.

3.) Indexes in the SAP HANA column store work very different than Oracles B*tree-Indexes. In case you actually create them, they cover all values, including NULLs.

So all in all:  just leave the index out and see if you can change the column to NOT NULL in order to keep your data clean (alternatively you may use DEFAULT values).

- Lars