[
https://issues.apache.org/jira/browse/PHOENIX-4712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16459885#comment-16459885
]
James Taylor commented on PHOENIX-4712:
---------------------------------------
I see what's going on. We're trying to be a bit too smart in keeping the
client-side cache up-to-date when changes are made (rather than invalidating
and letting the modified table metadata be pulled over from the server). For
example, the following tweak to your test passes (when a different connection
is used to execute the query). Rather than traversing the entire cache to
remove all views, it might be better to just invalidate the parent on the
client-side (i.e. remove it from the cache) when an index is added to it.
{code:java}
@Test
public void testQueryForViewOnTableThatHasIndex() throws SQLException {
try (Connection conn = DriverManager.getConnection(getUrl()); Connection conn2
= DriverManager.getConnection(getUrl()); Statement s = conn.createStatement();
Statement s2 = conn2.createStatement()) {
String tableName = generateUniqueName();
String viewName = generateUniqueName();
String indexName = generateUniqueName();
// Create a table
s.execute("create table " + tableName + " (col1 varchar primary key, col2
varchar)");
// Create a view on the table
s.execute("create view " + viewName + " (col3 varchar) as select * from " +
tableName);
// Create a index on the table
s.execute("create index " + indexName + " ON " + tableName + " (col2)");
try (ResultSet rs = s2.executeQuery("explain select /*+ INDEX(" + viewName + "
" + indexName + ") */ * from " + viewName + " where col2 = 'aaa'")) {
String explainPlan = QueryUtil.getExplainPlan(rs);
// check if the query uses the index
assertTrue(explainPlan.contains(indexName));
}
}
}
{code}
> When creating an index on a table, meta data cache of views related to the
> table isn't updated
> ----------------------------------------------------------------------------------------------
>
> Key: PHOENIX-4712
> URL: https://issues.apache.org/jira/browse/PHOENIX-4712
> Project: Phoenix
> Issue Type: Bug
> Reporter: Toshihiro Suzuki
> Assignee: Toshihiro Suzuki
> Priority: Major
> Attachments: PHOENIX-4712-v2.patch, PHOENIX-4712.patch
>
>
> Steps to reproduce are as follows:
> 1. Create a table
> {code}
> create table tbl (col1 varchar primary key, col2 varchar);
> {code}
> 2. Create a view on the table
> {code}
> create view vw (col3 varchar) as select * from tbl;
> {code}
> 3. Create a index on the table
> {code}
> create index idx ON tbl (col2);
> {code}
> After those, when issuing a explain query like the following, it seems like
> the query doesn't use the index, although the index should be used:
> {code}
> 0: jdbc:phoenix:> explain select /*+ INDEX(vw idx) */ * from vw where col2 =
> 'aaa';
> +---------------------------------------------------------------+
> | PLAN |
> +---------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER TBL |
> | SERVER FILTER BY COL2 = 'aaa' |
> +---------------------------------------------------------------+
> {code}
> However, after restarting sqlline, the explain output is changed, and the
> index is used.
> {code}
> 0: jdbc:phoenix:> explain select /*+ INDEX(vw idx) */ * from vw where col2 =
> 'aaa';
> +--------------------------------------------------------------------------------+
> | PLAN
> |
> +--------------------------------------------------------------------------------+
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER TBL
> |
> | SKIP-SCAN-JOIN TABLE 0
> |
> | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER IDX
> ['aaa'] |
> | SERVER FILTER BY FIRST KEY ONLY
> |
> | DYNAMIC SERVER FILTER BY "VW.COL1" IN ($3.$5)
> |
> +--------------------------------------------------------------------------------+
> {code}
> I think when creating an index on a table, meta data cache of views related
> to the table isn't updated, so the index isn't used for that query. However
> after restarting sqlline, the meta data cache is refreshed, so the index is
> used.
> When creating an index on a table, we should update meta data cache of views
> related to the table.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
