[jira] [Commented] (PHOENIX-1505) Support defining a VIEW over multiple tables

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ 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)