[
https://issues.apache.org/jira/browse/PHOENIX-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor updated PHOENIX-3370:
--
Attachment: PHOENIX-3370.patch
[~JipingZhou] - here's a patch with a test case. The reason the index isn't
being used is because the query is already a point lookup on the data table:
all the primary key columns have values: ORGANIZATION_ID, KEY_PREFIX, and INT1.
There's no more efficient way to retrieve data than a point lookup. In this
case, one row would be looked up and then a filter would be applied for the one
row on TEXT1. So you'd get back one or zero rows.
A query on only TEXT1 like the following that's in this new test case would
use the index, though:
{code}
SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW
WHERE ORGANIZATION_ID = '00Dxx002Col' AND TEXT1='Test'
{code}
> 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
> = '00Dxx002Col' 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 = '00Dxx002Col' 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)
