弹冠相庆

October 14, 2010

Oracle 11gr2对全外连接的优化

Filed under: Uncategorized — Corey @ 20:46
Tags:

看到yangtingkun的贴子,说11gr2会对全外连接生成更优的执行计划,看起来还是很给力的,查询效率高了很多。手中没有机器装了11gr2,所以以下结论没亲自测试。

摘录结论如下,更详细信息请查看原贴:

在以前的版本中,”全外连接由一个外连接和一个反连接UNION ALL来获得,因此每张表必须扫描两次。”

—————————————————————————–
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT     |      |    10 |   260 |    13   (8)| 00:00:01 |
|   1 |  VIEW                |      |    10 |   260 |    13   (8)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER |      |     9 |   234 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |     9 |   117 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     9 |   117 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI |      |     1 |    26 |     7  (15)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| T2   |     9 |   117 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| T1   |     9 |   117 |     3   (0)| 00:00:01 |
—————————————————————————–

在11gr2中,“Oracle优化了全外连接的执行计划,通过HASH JOIN FULL OUTER执行计划,使得每个表仅扫描一次,对比两个版本的逻辑读也可以看出,在11.2中全外连接的逻辑读减少了一半。”

———————————————————————————-
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT      |          |     9 |   234 |     9  (12)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |     9 |   234 |     9  (12)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |     9 |   234 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T1       |     9 |   117 |     4   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | T2       |     9 |   117 |     4   (0)| 00:00:01 |
———————————————————————————-

Update Oct 15, 2010

相对应的hint为NATIVE_FULL_OUTER_JOIN和NO_NATIVE_FULL_OUTER_JOIN

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: