[
https://issues.apache.org/jira/browse/PHOENIX-4712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16456714#comment-16456714
]
Thomas D'Silva commented on PHOENIX-4712:
-----------------------------------------
[~brfrn169]
Thanks for looking into this. I think its failing because we don't update the
views which are in the existing connection cache when we add an index to the
parent table.
However we cannot always just add the index to the ptable of the view. We need
to ensure that the index has all the columns required by the view, and we also
need to tack on the view where clause so that we don't access rows that can't
be accessed via the view when using the index (see addIndexesFromParentTable).
Maybe its easier to just remove the views of this table from the connection
cache, so that the next time they are resolved the index will be added to the
view if possible by addIndexesFromParentTable.
[~jamestaylor]
I am wondering if the view in the connection cache of other clients that didn't
create the index will be able to use the index. When we create an index on a
table, do we change the parent table timestamp so that all clients get the new
index when they resolve the parent table?
.
> 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.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)
