Jiping Zhou created PHOENIX-3370:
------------------------------------
Summary: VIEW derived from another VIEW with WHERE on a TABLE
doesn't use parent VIEW indexes
Key: PHOENIX-3370
URL: https://issues.apache.org/jira/browse/PHOENIX-3370
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.8.1
Reporter: Jiping Zhou
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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
