SAP HANA Full-Text

This article is a knowledge base for statements, system tables, system views and best practices around the SAP HANA fulltext search capabilities also including SAP HANA Search Rule Sets.

Full-Text Indexing

Create

CREATE FULLTEXT INDEX "FYI_ADDRESS_STREET" ON "ADDRESS_TABLE"("COLUMNNAME");

In HDI create a .hdbfulltextindex database artifact with the statement above, but leave out the "CREATE" keyword.

Monitoring

Monitoring of indexed documents in order to see the progress of creation or update of indexes.

SELECT * FROM SYS.M_FULLTEXT_QUEUES WHERE "SCHEMA_NAME" = '<SchemaNameHere>';

Metadata of fulltext indexes:

SELECT * FROM SYS.FULLTEXT_INDEXES WHERE "SCHEMA_NAME" = '<SchemaNameHere>';

Search Rule Sets

Monitoring

In order to check whether a search rule set was created in the database one can use the system view:

SELECT * FROM SEARCH_RULE_SETS;

This view joins the SYS.M_SEARCH_RULE_SETS_ AS with SYS.GRANTED_PRIVILEGES. As a result the user only sees what he has permissions to. In order to bypass the permissions requirement, i.e. to find out if a search rule was creates one could use a SYSTEM account to look at the SYS.M_SEARCH_RULE_SETS_ table directly:

SELECT * FROM SYS.M_SEARCH_RULE_SETS_;