/* with argument */SELECT /*+NO_MERGE(v)*/ t1.x, v.avg_y FROM t1, (SELECT x, AVG(y) AS avg_y FROM t2 GROUP BY x) v, WHERE t1.x = v.x AND t1.y = 1;/* in query block */SELECT t1.x, v.avg_y FROM t1, (SELECT /*+NO_MERGE*/ x, AVG(y) AS avg_y FROM t2 GROUP BY x) v, WHERE t1.x = v.x AND t1.y = 1;
FROM t1, (SELECT x, AVG(y) AS avg_y FROM t2 GROUP BY x) v,
WHERE t1.x = v.x AND t1.y = 1;/* in query block */SELECT t1.x, v.avg_y
FROM t1, (SELECT /*+NO_MERGE*/ x, AVG(y) AS avg_y FROM t2 GROUP BY x) v,
WHERE t1.x = v.x AND t1.y = 1;
如果在查询中用到多个视图,而组成这些视图的SQL语句都是优化好了的,单独访问任何一个视图,性能都没有问题。
如果此时不加NO_MERGE,则ORACLE会自动将若干个视图拆散,重新构造执行计划。
而事实证时,重新构造的执行计划往往会比较糟糕,于是,这种情况下就可以利用NO_MERGE(按字面理解就是不把若干个视图的查询条件进行合并),避免ORACLE将视图的查询拆散。