question about index in Hana

[Former Member]

As far as i know that hana can automatically create a "reverted index" in column table. i also notice in SQL reference guide, that there also "create index" and also we can chage "revert index type (hash/value) " in unique constrains.

so my quesiton is:

1. what is the use case of sentence "create index", whether this index is hana's inverted index or classical DB's index

2. in which case, we should mannually create or build index to replace the one hana automatically create for us.

any hint is very appreciate

[Former Member]

Hi William,

Genarally, you do not need to create index or rebuild index for column store table or view, you need to create text index by manually for colume store for text search.

You need to create index for row store table or view by manually, hana does not auto-create index for row store.

Hope this can help you.

Regards,

Jerry

[Former Member]

Hi Jerry,

Thanks for your response. does Hana create reverted index for each column in one table.

because from one document i catch these words

"

HANA automatically creates an index for all key columns. Columns with index are marked with ‚Index Type = FULL‛ in the table definition window:

Having an index for all key columns is usually sufficient as queries will typically put filter conditions on key columns. In use cases where filter conditions are on non-key fields and tables have many records, creating an index on this non-key field might improve the performance.

The syntax to create an index is

CREATE INDEX <name> ON <table> (<column>)

"

i guess it maybe a bit out-of-date, but i am also has 2 questions: (1) what is the key column; (2) how can i found these automatically index generated by Hana

very appreciate your kindly help

Regards,

William

[Former Member]

Hi William,

Yes, you understanding is right. This is memory store characteristic.

for Q1,  different value of every column has own memory location, it is also stored as index, you can check hana structure guide.

for Q2, I am not sure, late I confirmed that I will answered you Q2,sorry. I do not think that we can check or find in indexs table.

Regards,

Jerry

[Former Member]

Hi Jerry,

Thanks very much for your information sharing

[Former Member]

Hi William,

For Q2, you can check table indexs and index_columns in sys schema for auto-generated index related information, but you needs to has authority for them. I am using system account.

Hope this can help you.

Regards,

Jerry.

[Former Member]

Hello Jerry,

yes you are right, I can find the index type and index status form SYS.INDEXS and SYS.INDEX_COLUMNS view. Thanks for your help

Regards,

William

[lbreddemann]

Hi there,

for SYS.INDEXES and SYS.INDEX_COLUMNS there exist public synonyms and using SYSTEM user is neither recommended nor required.

In fact, it's a mistake/bug/error most of the times.

- Lars

[Former Member]

Hi Lars,

Yes, I know that not recommend to use SYSTEM user to check them.

I am not sure and confuse for "it's a mistake/bug/error most of the times", your means, using SYSTEM user is mistake/bug/error most of the times. Right?

Regards,

Jerry

[lbreddemann]

That's right. SYSTEM like any other pre-defined user/role should not be used for common tasks.

It's a boot-strap user that is there to setup the, well, the system.

- Lars

[Former Member]

Hi Lars,

Thank you for for explanation and advice, got it.

Cheers,

Jerry