phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (PHOENIX-3370) VIEW derived from another VIEW with WHERE on a TABLE doesn't use parent VIEW indexes
Date Wed, 12 Oct 2016 23:03:21 GMT

     [ 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)

Mime
View raw message