[
https://issues.apache.org/jira/browse/PHOENIX-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor resolved PHOENIX-3370.
-----------------------------------
Resolution: Invalid
> 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
> Assignee: James Taylor
> Attachments: PHOENIX-3370.patch, PHOENIX-3370_wip.patch
>
>
> 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)
