Local Indexing Not Looks Like Working As Expected

Hi,

We are accessing this table using a variety of different WHERE clauses and for each of them, we are trying to create an appropriate index to avoid full table scan. Since there is going to be almost 20 indexes, we tried to proceed with local indexing since there will be a lot of writes.

Here is the table definition:

CREATE TABLE DEVICEDIM_TYPE1 (
TENANT_ID VARCHAR NOT NULL,
DEVICE_TYPE1_KEY BIGINT NOT NULL,
CLASSNAME VARCHAR(64),
DAY_IN_MONTH SMALLINT,
MONTH_NUMBER SMALLINT,
QUARTER_NUMBER SMALLINT,
YEAR SMALLINT,
WEEK_NUMBER SMALLINT,
YEAR_FOR_WEEK SMALLINT,
HOUR SMALLINT,
MINUTE SMALLINT,
IPADDRESS VARCHAR(50),
DEVICENAME VARCHAR(255),
MACADDRESS VARCHAR(30),
CONSTRAINT PK PRIMARY KEY (TENANT_ID, DEVICE_TYPE1_KEY)
) SALT_BUCKETS=4, COMPRESSION='GZ', VERSIONS=1, MULTI_TENANT=TRUE;

And here is the index:

create local index gokhan_ix2 on devicedim_type1 (devicename, macaddress)

Now if I execute this:

explain select devicename from devicedim_type1 where tenant_id = 'ccd' and devicename = 'abc' and macaddress = 'afg'

Here is the output:

CLIENT 4-CHUNK PARALLEL 4-WAY RANGE SCAN OVER DEVICEDIM_TYPE1 [0,'ccd']
SERVER FILTER BY (DEVICENAME = 'abc' AND MACADDRESS = 'afg')
SERVER 100 ROW LIMIT
CLIENT 100 ROW LIMIT

I was expecting the index to be used. Am I wrong?