本文介绍如何通过设置db_keep_cache_size参数来利用KEEPCACHE特性提高查询效率，并展示了如何使用并行查询优化大型数据集的处理速度。具体包括设置缓存大小、缓存表格以及实现并行查询的SQL示例。
KEEP CACHE和并行查询简单使用
a)
1.Check db keep cache size:
show parameter db_keep_cache_size
if it is 0,please set it:
alter system set db_keep_cache_size=<size>;
2.keep table:
alter table <tablename> cache;
b) 并行查询
SELECT /*+PARALLEL(P,4)*/
ROW_NUMBER() OVER(ORDER BY P.PP, P.BEGDA) AS ROW_NUMBER,
P.*,
NVL((SELECT
WMSYS.WM_CONCAT(Z.ORGEH)
FROM (SELECT DISTINCT ORGEH, STEXT, OBJID FROM SNAP) Z
START WITH Z.ORGEH = P.ZSZBMID
CONNECT BY PRIOR Z.OBJID = Z.ORGEH),
P.ZSZBMID) ORGEHS
FROM TP1 P;
a)
1.Check db keep cache size:
show parameter db_keep_cache_size
if it is 0,please set it:
alter system set db_keep_cache_size=<size>;
2.keep table:
alter table <tablename> cache;
b) 并行查询
SELECT /*+PARALLEL(P,4)*/
ROW_NUMBER() OVER(ORDER BY P.PP, P.BEGDA) AS ROW_NUMBER,
P.*,
NVL((SELECT
WMSYS.WM_CONCAT(Z.ORGEH)
FROM (SELECT DISTINCT ORGEH, STEXT, OBJID FROM SNAP) Z
START WITH Z.ORGEH = P.ZSZBMID
CONNECT BY PRIOR Z.OBJID = Z.ORGEH),
P.ZSZBMID) ORGEHS
FROM TP1 P;
扫一扫
93
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
