oracle优化 外链接,Oracle 11g r2全外连接优化执行计划(一)

在11.2中，Oracle对于全外连接的执行计划进行了优化。

在以前的版本中，全外连接的执行计划如下：

SQL> SELECT * FROM V$VERSION;

BANNER

----------------------------------------------------------------

Oracle Database10gEnterpriseEdition Release10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30));

Table created.

SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30));

Table created.

SQL> INSERT INTO T1

2  SELECT ROWNUM - 1, OBJECT_NAME

3  FROM USER_OBJECTS

4  WHERE ROWNUM < 10;

9 rows created.

SQL> INSERT INTO T2

2  SELECT ROWNUM + 1, OBJECT_NAME

3  FROM USER_OBJECTS

4  WHERE ROWNUM < 10;

9 rows created.

SQL> SET AUTOT ON

SQL> SELECT T1.ID, T2.ID

2  FROM T1 FULL OUTER JOIN T2

3  ON T1.ID = T2.ID;

ID        ID

--------------------

2          2

3          3

4          4

5          5

6          6

7          7

8          8

1

0

10

9

11 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2841162349

-----------------------------------------------------------------------------

| 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 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("T1"."ID"="T2"."ID"(+))

6 - access("T1"."ID"="T2"."ID")

Note

-----

- dynamic sampling used for this statement

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

29  consistent gets

0  physical reads

0  redo size

718  bytes sent via SQL*Net to client

492  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

11  rows processed

全外连接由一个外连接和一个反连接UNION ALL来获得，因此每张表必须扫描两次。

而在11.2中，执行计划为：

SQL> SELECT * FROM V$VERSION;

BANNER

--------------------------------------------------------------------------------

Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T1

2  SELECT ROWNUM - 1, OBJECT_NAME

3  FROM USER_OBJECTS

4  WHERE ROWNUM < 10;

已创建9行。

SQL> INSERT INTO T2

2  SELECT ROWNUM + 1, OBJECT_NAME

3  FROM USER_OBJECTS

4  WHERE ROWNUM < 10;

已创建9行。

SQL> SET AUTOT ON

SQL> SELECT T1.ID, T2.ID

2  FROM T1 FULL OUTER JOIN T2

3  ON T1.ID = T2.ID;

ID        ID

--------------------

2          2

3          3

4          4

5          5

6          6

7          7

8          8

9

10

1

0

已选择11行。

执行计划

----------------------------------------------------------

Plan hash value: 53297166

----------------------------------------------------------------------------------

| 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 |

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("T1"."ID"="T2"."ID")

Note

-----

- dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

0  recursive calls

0  db block gets

15  consistent gets

0  physical reads

0  redo size

733  bytes sent via SQL*Net to client

519  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

11  rows processed

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

oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html