Precomputation and Materialized Views

今天132b上课讲DB的preocomputation。

Materialized Views

Wiki Page

 In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary based on aggregations of a table's data.The process of creating a materialized view is sometimes called materialization. It is sometimes described as a form of precomputation. As with other forms of precomputation, materialized views are typically created for performance reasons, i.e. as a form of optimization.

 In any database management system following the relational model, a view is a virtual table representing the result of a database query. Whenever a query or an update addresses an ordinary view's virtual table, the DBMS converts these into queries or updates against the underlying base tables. A materialized view takes a different approach in which the query result is cached as a concrete table that may be updated from the original base tables from time to time. This enables much more efficient access, at the cost of some data being potentially out-of-date.

What is the difference between Views and Materialized Views

 Materialized views are disk based and update periodically base upon the query definition.

Views are virtual only and run the query definition each time they are accessed.

 Also when you need performance on data that don't need to be up to date to the very second, materialized views are better, but your data will be older than in a standard view.

Precomputation

Precomputation是和Materialized View对应的。

[code language="sql"]

SELECT e.deptNo, max(salary) AS sal
FROM employee e, dept d
WHERE e.deptNo = d.dno
GROUP BY e.deptNo

[/code]

可先precompute

[code language="sql"]
CREATE TABLE Q’
INSERT INTO Q’
(
SELECT e.deptNo, max(salary) AS sal
FROM employee e, dept d
WHERE e.deptNo = d.dno
GROUP BY e.deptNo
)
[/code]

一些SQL巧用

text转时间

在数据库中我以text存储时间，但是这样不能直接比较时间大小，故寻得此法：

[code language="sql"]
SELECT *
FROM table
WHERE CAST(StartTime As Time) > CAST(EndTime As Time)
[/code]

感觉就是强制类型转换，将类似13:00的text转化为time。

SQL中的for循环

决定暂时不用for循环，for循环打算写在jsp里。我还有一些其他需求，基本也都找到了方法。

date BETWEEN

在数据库里我以text ‘YYYY-MM-DD’形式保存时间，但是遇到在两个时间区段取时间的问题。PostgreSQL可以照常使用BETWEEN来检查。

[code language="sql"]
SELECT *
FROM review
WHERE review_date BETWEEN '2014-01-01' AND '2014-03-01'
[/code]

get day of week

PostgreSQL内部也有取已知日期是星期几的功能。

[code language="sql"]

SELECT review_date, extract(dow from review_date::timestamp)
FROM review
WHERE review_date BETWEEN '2014-01-01' AND '2014-03-01'

[/code]

这么一来，我在review表里存的星期信息就多余了，还得改表。

Getting date list in a range in PostgreSQL

还有，给起点终点，我想取出一段连续的日期，如下。

[code language="sql"]

SELECT aval_date::date
FROM generate_series('2012-06-29',
'2012-07-03', '1 day'::interval) aval_date

[/code]

代码不难，我卡住了。我想把这个aval_date作为大SQL语句WHERE的一部分，结果愣是想不出怎么写。后来测试一下SELECT * 发现默认的attribute就是aval_date。这下直接去大SQL把WHERE中写成aval_date=...就行了。

这个review的available的date time的SQL算是写好了，可能对另一个题也有启发。继续！

Getting date list in a range in PostgreSQL

关于临时表

之前对TEMP TABLE的理解就是中间变量。后来Wizard告诉我，TEMP TABLE多用在建出来一个马上要访问很多次取结果的或者中间结果太大放不下内存的。看来需要改动一些，放在同一个query语句中会更好，就用nested query吧。

更新：目前nested query果然好用，不理temp table了！