[ 
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 = '00Dxx0000002Col' 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 
> = '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)

Reply via email to