[ https://issues.apache.org/jira/browse/PHOENIX-4712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16456582#comment-16456582 ]
Toshihiro Suzuki commented on PHOENIX-4712: ------------------------------------------- I just attached a v1 patch. > 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)