Multiple search indexes for a single table (Public preview) - Lindorm

This topic describes how to create multiple search indexes for a single primary table.

Prerequisites

The LindormTable engine for Lindorm must be version 2.8.2.3 or later. For more information about how to view or upgrade the engine version, see minor version update.
The Lindorm LTS engine must be version 3.8.12.5 or later. For more information about how to view or upgrade the engine version, see Minor version update.

Limits

You can create a maximum of five search indexes on a single primary table.
Search indexes on the same primary table cannot share names with each other or with other types of indexes.

Usage

Create a wide table.
```
create table testTable (pk int, c1 int, c2 varchar, c3 long, primary key(pk));
```
Note
The multiple search index feature is in public preview. To use this feature, you must contact Lindorm technical support on DingTalk (ID: s0s3eg3) to enable it.

Create multiple search indexes on the same wide table. The index columns and properties can be the same for each search index.

create index idx1 using search on testTable (c1,c2);
create index idx2 using search on testTable (c3);
create index idx3 using search on testTable (c1,c2,c3);

Write data to the wide table. The data is automatically synchronized to each search index.

upsert into testTable(pk,c1,c2,c3) values (1,1,'1',1);
upsert into testTable(pk,c1,c2,c3) values (2,2,'2',2);
upsert into testTable(pk,c1,c2,c3) values (3,3,'3',3);
upsert into testTable(pk,c1,c2,c3) values (4,4,'4',4);

Query the data. Lindorm automatically selects an appropriate search index based on the query conditions. If multiple search indexes cover the query, one of them is selected.
1. Query the c1, c2, and c3 columns. The idx3 index is automatically selected.
```
select * from testTable where c1>0 and c2>'0' and c3>0;
```
2. Query the c1 and c2 columns. Both the idx1 and idx3 indexes can cover the query. Either the idx1 or idx3 index is selected.
```
select * from testTable where c1>0 and c2>'0';
```
3. Query the c3 column. Both the idx2 and idx3 indexes can cover the query. Either the idx2 or idx3 index is selected.
```
select * from testTable where c3>0;
```
Explicitly specify an index for a query.
You can use the HINT syntax to explicitly specify a search index for a query.
```
select /*+ _l_force_index_('idx1') */ * from testTable where c1>0 and c2>'0';
```

Control the status of an index.

You can use the ALTER INDEX statement to independently control the status of each index.

alter index idx1 on testTable inactive;
alter index idx1 on testTable disabled;

Status value	Description
ACTIVE	The search index is available for queries. Data in the wide table is automatically indexed.
INACTIVE	The search index is not available for queries. However, data in the wide table is still automatically indexed.
DISABLED	The search index is not available for queries. Data in the wide table is not indexed.

Note

A search index in the DISABLED status cannot be changed to another status. To re-enable the index, you must run the REBUILD command.