32.读书笔记收获不止Oracle之表链接的驱动顺序

于 2017-11-06 18:16:03 发布
阅读量506 收藏
点赞数
分类专栏： 收获不止Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/notbaron/article/details/78460356
版权

32.读书笔记收获不止Oracle之表链接的驱动顺序

1.  嵌套循环驱动顺序

T1和T2表草考前面笔记。

SQL>Alter session set statistics_level=all;

1.1      实验1

SQL>select /*+leading(t1) use_nl(t2)*/ * from t1,t2 wheret1.id=t2.t1_id and t1.n=19;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstatslast'));

PLAN_TABLE_OUTPUT

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

SQL_ID   2w8kmgu3tmxhq,child number 0

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

select /*+leading(t1) use_nl(t2)*/ * fromt1,t2 where t1.id=t2.t1_id

and t1.n=19

 

Plan hash value: 1967407726

 

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

-----

 

| Id | Operation                     | Name | Starts | E-Rows | A-Rows |         A-Time   | Buff

 

PLAN_TABLE_OUTPUT

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

ers |

 

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

-----

 

|   0| SELECT STATEMENT   |      |           1 |              |         1 |00:00:00.01 |    1

014 |

 

|   1|  NESTED LOOPS     |          |           1 |            1 |          1 |00:00:00.01 |    1

014 |

 

 

PLAN_TABLE_OUTPUT

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

|*  2|   TABLE ACCESS FULL| T1   |         1 |            1 |          1 |00:00:00.01 |

  8 |

 

|*  3|   TABLE ACCESS FULL| T2   |         1 |            1 |          1 |00:00:00.01 |    1

006 |

 

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

-----

 

 

Predicate Information (identified byoperation id):

 

PLAN_TABLE_OUTPUT

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

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

 

   2- filter("T1"."N"=19)

   3- filter("T1"."ID"="T2"."T1_ID")

22 rows selected.

1.2      实验2

SQL>select /*+leading(t2) use_nl(t1)*/ * from t1,t2 wheret1.id=t2.t1_id and t1.n=19;

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID   axb2hvwr253j8,child number 0

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

select /*+leading(t2) use_nl(t1)*/ * fromt1,t2 where t1.id=t2.t1_id

and t1.n=19

 

Plan hash value: 4016936828

 

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

-----

 

| Id | Operation                     | Name | Starts | E-Rows | A-Rows |         A-Time   | Buff

 

PLAN_TABLE_OUTPUT

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

ers |

 

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

-----

 

|   0| SELECT STATEMENT   |      |           1 |              |         1 |00:00:00.93 |

701K|

 

|   1|  NESTED LOOPS     |          |           1 |            1 |          1 |00:00:00.93 |

701K|

 

 

PLAN_TABLE_OUTPUT

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

|   2|   TABLE ACCESS FULL| T2   |         1 |          100K|      100K|00:00:00.01 |    1

006 |

 

|*  3|   TABLE ACCESS FULL| T1   |   100K|      1 |          1 |00:00:00.89 |

700K|

 

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

-----

 

 

Predicate Information (identified byoperation id):

 

PLAN_TABLE_OUTPUT

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

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

 

   3- filter(("T1"."ID"="T2"."T1_ID" AND"T1"."N"=19))

21 rows selected.

采用不同的驱动表，使用的BUFFER是不一样的，t1为驱动表的时候是1014，t2为驱动表的话使用了701k大小。

T1作为驱动表访问的情况下，T2表只被访问了1次。而t2 表作为驱动表被访问的情况下，T1表被访问了10000次，因为T1结果返回1条记录，而T2结构返回10000条记录。

嵌套循环连接要注意驱动表的顺序。

2.  哈希连接的表驱动顺序

2.1      实验1

SQL>select /*+leading(t1) use_hash(t2)*/ * from t1,t2 wheret1.id=t2.t1_id and t1.n=19;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstatslast'));

PLAN_TABLE_OUTPUT

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

SQL_ID   0x20q3zf8pn79,child number 0

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

select /*+leading(t1) use_hash(t2)*/ * fromt1,t2 where t1.id=t2.t1_id

and t1.n=19

 

Plan hash value: 1838229974

 

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

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

 

| Id | Operation                     | Name | Starts | E-Rows | A-Rows |         A-Time   | Buff

 

PLAN_TABLE_OUTPUT

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

ers | OMem |  1Mem | Used-Mem |

 

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

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

 

|   0| SELECT STATEMENT   |      |           1 |              |         1 |00:00:00.05 |    1

013 |           |          |             |

 

|*  1|  HASH JOIN              |         |           1 |           1 |          1 |00:00:00.05 |    1

013 |       960K|      960K| 403K (0)|

 

 

PLAN_TABLE_OUTPUT

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

|*  2|   TABLE ACCESS FULL| T1   |         1 |            1 |          1 |00:00:00.01 |

  7 |          |          |             |

 

|   3|   TABLE ACCESS FULL| T2   |         1 |          100K|      100K|00:00:00.01 |    1

006 |           |         |             |

 

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

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

 

 

Predicate Information (identified byoperation id):

 

PLAN_TABLE_OUTPUT

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

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

 

   1- access("T1"."ID"="T2"."T1_ID")

   2- filter("T1"."N"=19)

 

 

22 rows selected.

 

2.2      实验2

SQL>select /*+leading(t2) use_hash(t1)*/* from t1,t2 where t1.id=t2.t1_id and t1.n=19;

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID   1m1vnuurcxcm3,child number 0

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

select /*+leading(t2) use_hash(t1)*/ * fromt1,t2 where t1.id=t2.t1_id

and t1.n=19

 

Plan hash value: 2959412835

 

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

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

 

| Id | Operation                     | Name | Starts | E-Rows | A-Rows |         A-Time   | Buff

 

PLAN_TABLE_OUTPUT

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

ers | OMem |  1Mem | Used-Mem |

 

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

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

 

|   0| SELECT STATEMENT   |      |           1 |              |         1 |00:00:00.04 |    1

013 |           |          |             |

 

|*  1|  HASH JOIN              |         |           1 |           1 |          1 |00:00:00.04 |    1

013 |       11M|  2469K|  12M (0)|

 

 

PLAN_TABLE_OUTPUT

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

|   2|   TABLE ACCESS FULL| T2   |         1 |          100K|      100K|00:00:00.01 |    1

005 |           |          |             |

 

|*  3|   TABLE ACCESS FULL| T1   |         1 |            1 |          1 |00:00:00.01 |

  8 |           |          |             |

 

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

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

 

 

Predicate Information (identified byoperation id):

 

PLAN_TABLE_OUTPUT

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

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

 

   1- access("T1"."ID"="T2"."T1_ID")

   3- filter("T1"."N"=19)

 

 

22 rows selected.

使用的BUFFER是一致的，Used-Mem 实验1是403k,实验2是12M。

在哈希连接中驱动表的顺序也是非常重要的。

3.  排序合拼连接的表驱动顺序

3.1      实验1

SQL>select /*+leading(t1) use_merge(t2)*/ * from t1,t2 wheret1.id=t2.t1_id and t1.n=19;

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID   afuusm6140307,child number 0

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

select /*+leading(t1) use_merge(t2)*/ *from t1,t2 where t1.id=t2.t1_id

and t1.n=19

 

Plan hash value: 412793182

 

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

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

 

| Id | Operation                      | Name | Starts | E-Rows | A-Rows |   A-Time  | Buf

 

PLAN_TABLE_OUTPUT

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

fers |       OMem|                  1Mem | Used-Mem |

 

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

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

 

|   0| SELECT STATEMENT    |     |         1 |            |       1 |00:00:00.07 |

1012 |          |        |                     |

 

|   1|  MERGE JOIN            |         |         1 |          1 |         1 |00:00:00.07 |

1012 |          |        |                     |

 

 

PLAN_TABLE_OUTPUT

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

|   2|   SORT JOIN             |         |         1 |          1 |         1 |00:00:00.01 |

   7|     2048 |     2048 | 2048  (0)|

 

|*  3|    TABLE ACCESS FULL| T1   |        1 |          1 |         1 |00:00:00.01 |

   7|         |        |                     |

 

|*  4|   SORT JOIN             |         |         1 |       100K|       1|00:00:00.07 |

1005 |     9762K|   1209K| 8677K (0)|

 

|   5|    TABLE ACCESS FULL| T2   |        1 |       100K|    100K|00:00:00.02 |

1005 |          |        |                     |

 

PLAN_TABLE_OUTPUT

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

 

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

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

 

 

Predicate Information (identified byoperation id):

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

 

   3- filter("T1"."N"=19)

   4- access("T1"."ID"="T2"."T1_ID")

      filter("T1"."ID"="T2"."T1_ID")

 

PLAN_TABLE_OUTPUT

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

 

 

25 rows selected.

 

3.2      实验2

SQL>select /*+leading(t2) use_merge(t1)*/ * from t1,t2 wheret1.id=t2.t1_id and t1.n=19;

SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

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

SQL_ID   dz2rnd44fg2jb,child number 0

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

select /*+leading(t2) use_merge(t1)*/ *from t1,t2 where t1.id=t2.t1_id

and t1.n=19

 

Plan hash value: 1792967693

 

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

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

 

| Id | Operation                      | Name | Starts | E-Rows | A-Rows |   A-Time  | Buf

 

PLAN_TABLE_OUTPUT

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

fers |       OMem|                  1Mem | Used-Mem |

 

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

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

 

|   0| SELECT STATEMENT    |     |         1 |            |       1 |00:00:00.06 |

1012 |          |        |                     |

 

|   1|  MERGE JOIN            |         |         1 |          1 |         1 |00:00:00.06 |

1012 |          |        |                     |

 

 

PLAN_TABLE_OUTPUT

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

|   2|   SORT JOIN             |         |         1 |       100K|      20|00:00:00.06 |

1005 |     9762K|   1209K| 8677K (0)|

 

|   3|    TABLE ACCESS FULL| T2   |        1 |       100K|    100K|00:00:00.01 |

1005 |          |        |                     |

 

|*  4|   SORT JOIN             |         |        20 |          1 |         1 |00:00:00.01 |

   7|     2048 |     2048 | 2048  (0)|

 

|*  5|    TABLE ACCESS FULL| T1   |        1 |          1 |         1 |00:00:00.01 |

   7|         |        |                     |

 

PLAN_TABLE_OUTPUT

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

 

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

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

 

 

Predicate Information (identified byoperation id):

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

 

   4- access("T1"."ID"="T2"."T1_ID")

      filter("T1"."ID"="T2"."T1_ID")

   5- filter("T1"."N"=19)

 

PLAN_TABLE_OUTPUT

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

25 rows selected.

排序合并连接实验1和实验2 的效率是一样的，

 

小结：

嵌套循环连接和哈希连接有驱动顺序，驱动表的顺序不同将影响表连接的性能，而排序合并连接没有驱动的概念，无论哪张表在前都无妨。

badman250
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值