Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Invalid
-
4.8.1
-
None
-
None
-
None
Description
1. Create a global table
CREATE TABLE IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_TABLE (
ORGANIZATION_ID CHAR(15) NOT NULL,
KEY_PREFIX CHAR(3) NOT NULL,
CREATED_DATE DATE,
CREATED_BY CHAR(15),
CONSTRAINT PK PRIMARY KEY (
ORGANIZATION_ID,
KEY_PREFIX
)
) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true;
2. Create a global view on base global table with where clause
CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_VIEW (
INT1 BIGINT NOT NULL,
DOUBLE1 DECIMAL(12, 3),
IS_BOOLEAN BOOLEAN,
TEXT1 VARCHAR,
CONSTRAINT PKVIEW PRIMARY KEY
(
INT1
)
)
AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE WHERE KEY_PREFIX = '123';
3. Create gloabl index on the global view
CREATE INDEX IF NOT EXISTS GLOBAL_INDEX
ON PLATFORM_ENTITY.GLOBAL_VIEW (TEXT1 DESC, INT1)
INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE);
4. Create tenant specific view on top of global view
CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.TENANT_VIEW
AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_VIEW;
5. Query in global view
EXPLAIN SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW WHERE ORGANIZATION_ID = '00Dxx0000002Col' AND TEXT1='Test' AND INT1=1;
The result is
CLIENT 1-CHUNK 1 ROWS 452 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 1 KEY OVER PLATFORM_ENTITY.GLOBAL_TABLE
SERVER FILTER BY TEXT1 = 'Test'
It is not using the secondary index at all. The same thing happens on the Tenant view.
However if we have a global view without where clause like
CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_VIEW2 (
INT1 BIGINT NOT NULL,
DOUBLE1 DECIMAL(12, 3),
IS_BOOLEAN BOOLEAN,
TEXT1 VARCHAR,
CONSTRAINT PKVIEW PRIMARY KEY
(
INT1
)
)
AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE;
CREATE INDEX IF NOT EXISTS GLOBAL_INDEX2
ON PLATFORM_ENTITY.GLOBAL_VIEW2 (TEXT1 DESC, INT1)
INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE);
EXPLAIN SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW2 WHERE ORGANIZATION_ID = '00Dxx0000002Col' AND TEXT1='Test' AND INT1=1 ORDER BY TEXT1 DESC, INT1;
The secondary index will be correctly used.