[jira] [Commented] (PHOENIX-3370) VIEW derived from another VIEW with WHERE on a TABLE doesn't use parent VIEW indexes
[ https://issues.apache.org/jira/browse/PHOENIX-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15607535#comment-15607535 ] Hudson commented on PHOENIX-3370: - SUCCESS: Integrated in Jenkins build Phoenix-master #1450 (See [https://builds.apache.org/job/Phoenix-master/1450/]) PHOENIX-3370 VIEW derived from another VIEW with WHERE on a TABLE (jamestaylor: rev 202b8eb1eda29db01006cdeefd8199f0bd360692) * (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/index/ViewIndexIT.java > 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)
[jira] [Commented] (PHOENIX-3370) VIEW derived from another VIEW with WHERE on a TABLE doesn't use parent VIEW indexes
[ https://issues.apache.org/jira/browse/PHOENIX-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15570319#comment-15570319 ] Hudson commented on PHOENIX-3370: - SUCCESS: Integrated in Jenkins build Phoenix-master #1439 (See [https://builds.apache.org/job/Phoenix-master/1439/]) PHOENIX-3370 VIEW derived from another VIEW with WHERE on a TABLE (jamestaylor: rev 89cea911c1b5429363d32cbdeac3dadddccd5465) * (edit) phoenix-core/src/test/java/org/apache/phoenix/compile/QueryCompilerTest.java > 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)
