Toshihiro Suzuki created PHOENIX-4712:
-----------------------------------------
Summary: 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
Steps to reproduce are as follows:
1. Create a table
{code}
create table tbl (aaa varchar primary key, bbb varchar);
{code}
2. Create a view on the table
{code}
create view vw (ccc varchar) as select * from tbl;
{code}
3. Create a index on the table
{code}
create index idx ON tbl (bbb);
{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 bbb =
'aaa';
+---------------------------------------------------------------+
| PLAN |
+---------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER TBL |
| SERVER FILTER BY BBB = '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 bbb =
'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.AAA" 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)
