[
https://issues.apache.org/jira/browse/PHOENIX-1505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16052455#comment-16052455
]
Julian Hyde commented on PHOENIX-1505:
--------------------------------------
In the Calcite branch, defining views and expanding them in queries is very
straightforward. I suggest that we allow arbitrary SELECT statements in views
(JOIN, UNION, GROUP BY, and even ORDER BY), so this could cover PHOENIX-1506
and PHOENIX-1507 also.
The only wrinkle is DML. Complex views won't support DML, because there may not
be a well-defined row underlying each output row. DML-capable views have an
underlying table (or view, which must be DML-capable).
Calcite can figure out default expressions for all columns that are filtered &
projected away by a DML-capable view. (This is by definition. If Calcite can't
figure it out, the view is not considered DML-capable.)
Also, DML-incapable views do not allow schema extensions (the EXTEND clause).
In a project view that is DML-capable, some columns might not have base
columns. For example, {{CREATE VIEW v AS SELECT empno, deptno, 10 as ten FROM
Emp}}. You would not be able to specify {{ten}} in a DML statement.
I have see several JIRA cases talking about indexes on views. In the Calcite
branch, do these provide any advantages over indexes on tables? If I'd defined
an index, I would hope that my query would use it, and my DML statement would
cause it to be modified, regardless of whether my statement references the
table or the view.
> Support defining a VIEW over multiple tables
> --------------------------------------------
>
> Key: PHOENIX-1505
> URL: https://issues.apache.org/jira/browse/PHOENIX-1505
> Project: Phoenix
> Issue Type: Sub-task
> Reporter: James Taylor
> Labels: SFDC
>
> Our current view implementation only supports views over a single table. We
> should enhance this to support creating a view over multiple tables. For
> example: CREATE VIEW v AS SELECT * FROM DEPT d, EMPL e WHERE d.dept_id =
> e.dept_id
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
