[jira] [Updated] (CALCITE-46) Materialized views

["Michael Mior (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-46?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Michael Mior updated CALCITE-46:
--------------------------------
    Description: 
 Support materialized views. A materialized view is a table that contains the same data as a query. It is a "win" if it is cheaper to read from the table than to execute the query. It can be used to make other queries more efficient.

1. define the materialized view in the schema, using syntax similar to how you define a view, except that you also specify a table.
2. populate: whether to populate on first use. (future options: populate on schema load, in either background or foreground.)
3. add the table to the planner's search space for relevant queries, so that the planner can map queries onto it.

Sub-types of materialized views:

1. union/partition
 select * from t1 where year = 2013 union all select * from t2 where year < 2013
2. aggregate  
 select c1, c2, agg(c3) from t1 join t2 join t3 group by c1, c2
3. sort/project/distinct
 select distinct country, state, population from customer order by country, state

Each of these are important in analytical databases. Test each specifically. May need to add canonical forms so that planner can map queries onto these types of views and not be fooled by cosmetic stuff like different projections.

---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/46
Created by: [julianhyde|https://github.com/julianhyde]
Labels: enhancement, for-next-release, 
Created at: Sat Jun 29 00:13:59 CEST 2013
State: open


  was:
Support materialized views. A materialized view is a table that contains the same data as a query. It is a "win" if it is cheaper to read from the table than to execute the query. It can be used to make other queries more efficient.

1. define the materialized view in the schema, using syntax similar to how you define a view, except that you also specify a table.
2. populate: whether to populate on first use. (future options: populate on schema load, in either background or foreground.)
3. add the table to the planner's search space for relevant queries, so that the planner can map queries onto it.

Sub-types of materialized views:

1. union/partition
 select * from t1 where year = 2013 union all select * from t2 where year < 2013
2. aggregate  
 select c1, c2, agg(c3) from t1 join t2 join t3 group by c1, c2
3. sort/project/distinct
 select distinct country, state, population from customer order by country, state

Each of these are important in analytical databases. Test each specifically. May need to add canonical forms so that planner can map queries onto these types of views and not be fooled by cosmetic stuff like different projections.

---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/46
Created by: [julianhyde|https://github.com/julianhyde]
Labels: enhancement, for-next-release, 
Created at: Sat Jun 29 00:13:59 CEST 2013
State: open



> Materialized views
> ------------------
>
>                 Key: CALCITE-46
>                 URL: https://issues.apache.org/jira/browse/CALCITE-46
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: GitHub Import
>            Priority: Major
>              Labels: github-import
>
>  Support materialized views. A materialized view is a table that contains the same data as a query. It is a "win" if it is cheaper to read from the table than to execute the query. It can be used to make other queries more efficient.
> 1. define the materialized view in the schema, using syntax similar to how you define a view, except that you also specify a table.
> 2. populate: whether to populate on first use. (future options: populate on schema load, in either background or foreground.)
> 3. add the table to the planner's search space for relevant queries, so that the planner can map queries onto it.
> Sub-types of materialized views:
> 1. union/partition
>  select * from t1 where year = 2013 union all select * from t2 where year < 2013
> 2. aggregate  
>  select c1, c2, agg(c3) from t1 join t2 join t3 group by c1, c2
> 3. sort/project/distinct
>  select distinct country, state, population from customer order by country, state
> Each of these are important in analytical databases. Test each specifically. May need to add canonical forms so that planner can map queries onto these types of views and not be fooled by cosmetic stuff like different projections.
> ---------------- Imported from GitHub ----------------
> Url: https://github.com/julianhyde/optiq/issues/46
> Created by: [julianhyde|https://github.com/julianhyde]
> Labels: enhancement, for-next-release, 
> Created at: Sat Jun 29 00:13:59 CEST 2013
> State: open



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)