materialized view
主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,而从快速的得到结果。
使用语法:
CREATE MATERIALIZED VIEW XX
REFRESH [[fast | complete | force]
[on demand | commit]
[start with date]
[next date]
[with {primary key | rowid}]
]
[ENABLE | DISABLE] QUERY REWRITE
递归查询
start with ...指定查询的根行
connect by prior... 父行和子行的关系(是以父行为主)
ORDER SIBLINGS BY ... 同级别排序
例:
create materialized view mv_auth_ycps
refresh force on demand
start with to_date('2013-6-25 01:30:00','yyyy-mm-dd hh24:mi:ss')next sysdate+1
as
select *
from acc_domain
where dtype='domain'
start with parent_id is null
connect by prior id = parent_id
ORDER SIBLINGS BY position;
参考资料:http://www.cnblogs.com/lizhe88/archive/2011/01/23/1942423.html