phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Julian Hyde (JIRA)" <>
Subject [jira] [Commented] (PHOENIX-1505) Support defining a VIEW over multiple tables
Date Fri, 16 Jun 2017 22:33:00 GMT


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:
>             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

View raw message