oracle 开启诊断功能,开始看Oracle性能诊断艺术（第2版）

╰半夏微凉° 2023-01-18 02:19 170阅读 0赞

本帖最后由 〇〇 于 2016-6-14 09:10 编辑

http://www.itpub.net/thread-1873843-1-1.html

还没有发现翻译错误，比英文的看得快多了，比如就发现了这个功能，译者把它叫做联接因式分解

The following is an example based on the join\_factorization.sql script. Notice that both component queries

not only access the same tables, they also apply a restriction to the same table (t2). Without this query transformation,

both component queries would be executed idependently, and both tables would be accessed twice:

SELECT \*

FROM t1, t2

WHERE t1.id = t2.id AND t2.id < 10

UNION ALL

SELECT \*

FROM t1, t2

WHERE t1.id = t2.id AND t2.id > 990

To avoid the repetitive processing of accessing each table twice, join factorization can transform the query, as

shown in the following example. Because table t1 is factorized, it’s accessed only once. Depending on the table’s size

and the access path chosen to extract data from it, the savings in term of I/O and CPU utilization can be huge:

SELECT t1.\*, vw\_jf.\*

FROM t1, (SELECT \*

FROM t2

WHERE id < 10

UNION ALL

SELECT \*

FROM t2

WHERE id > 990) vw\_jf

WHERE t1.id = vw\_jf.id

还有全外联接转换成union all反联接

SELECT \*

FROM t1 FULL OUTER JOIN t2 ON t1.n = t2.n

The query transformation produces the following query. Notice that only the first of the two joins defined in the

inline view is an outer join. The second one contains, as explained earlier in the “Subquery Unnesting” section,

a special operator (a=) to implement the anti-join:

SELECT id1 AS id, n1 AS n, pad1 AS pad, id, t1\_id, n, pad

FROM (SELECT t1.id AS id1, t1.n AS n1, t1.pad AS pad1, t2.id, t2.t1\_id, t2.n, t2.pad

FROM t1, t2

WHERE t1.n = t2.n(+)

UNION ALL

SELECT NULL, NULL, NULL, t2.id, t2.t1\_id, t2.n, t2.pad

FROM t1, t2

WHERE t1.n a= t2.n) vw\_foj

oracle 开启诊断功能,开始看Oracle性能诊断艺术（第2版）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Oracle：查询性能低下，如何诊断优化？

相关 Oracle数据库性能瓶颈诊断：实例分享

相关 Oracle数据库性能瓶颈诊断与解决

相关 Oracle数据库性能瓶颈诊断与调整

相关 Oracle查询性能低下：诊断与优化

相关 oracle 开启诊断功能,开始看Oracle性能诊断艺术（第2版）

相关 Oracle 诊断临时暴涨的诊断

相关 Oracle ADDM性能诊断利器及报告解读

相关 Oracle 自动故障诊断

相关 Oracle诊断事件列表