36.读书笔记收获不止Oracle之表哈希、合并排序连接与索引

36.读书笔记收获不止Oracle之表哈希、合并排序连接与索引

1.  哈希连接与索引

对于哈希连接和排序合并连接，索引的连接条件起不到快速检索的作用。但是限制条件列如果有合适的索引可以快速检索到少量记录，是可以提升性能的。

                  在没有任何索引的情况下，ORACLE倾向走哈希连接，因为哈希连接算法本身还是比较先进的。哈希连接需要在PGA中的HASH_AREA_SIZE中完成。

2.  合并排序连接与索引

索引对哈希连接来说，仅仅是考虑限制条件上的索引是否能用上索引，连接条件上的索引是不能发挥作用的。排序合并连接和哈希连接有差别，排序合并连接上的连接条件没有检索作用，却有消除排序的作用。

                  实验如下：

SQL>alter session set statistics_level=all;

SQL>select /*+ordered use_merge(t2)*/ * from t1,t2 wheret1.id=t2.t1_id;

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

PLAN_TABLE_OUTPUT

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

SQL_ID   5jxwkr07a1jdp,child number 0

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

select /*+ordered use_merge(t2)*/ * fromt1,t2 where t1.id=t2.t1_id

 

Plan hash value: 412793182

 

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

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

 

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

fers |       OMem|                  1Mem | Used-Mem |

 

PLAN_TABLE_OUTPUT

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

 

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

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

 

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

1012 |          |        |                     |

 

|   1|  MERGE JOIN            |         |         1 |       100 |       100|00:00:00.05 |

1012 |          |        |                     |

 

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

 

PLAN_TABLE_OUTPUT

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

   7| 13312 | 13312 |12288  (0)|

 

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

   7|         |        |                     |

 

|*  4|   SORT JOIN             |         |        100 |       100K|    100 |00:00:00.05 |

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

 

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

1005 |          |        |                     |

 

 

PLAN_TABLE_OUTPUT

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

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

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

 

 

Predicate Information (identified byoperation id):

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

 

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

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

23 rows selected.

建索引观察，在T1表的连接条件ID列上建索引，然后观察。

Create index idx_t1_id on t1(id);

SQL>select /*+ordered use_merge(t2)*/ * from t1,t2 wheret1.id=t2.t1_id;

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

PLAN_TABLE_OUTPUT

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

SQL_ID   5jxwkr07a1jdp,child number 0

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

select /*+ordered use_merge(t2)*/ * fromt1,t2 where t1.id=t2.t1_id

 

Plan hash value: 2678642687

 

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

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

 

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

A-Time    | Buffers |  OMem |  1Mem | Used-Mem |

 

PLAN_TABLE_OUTPUT

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

 

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

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

 

|   0| SELECT STATEMENT                   |                     |             1 |               |        100 |00:

00:00.06 |    1021 |            |           |              |

 

|   1|  MERGE JOIN                              |                     |             1 |    100 |         100|00:

00:00.06 |    1021 |            |           |              |

 

|   2|   TABLE ACCESS BY INDEX ROWID| T1            |             1|    100 |         100 |00:

 

PLAN_TABLE_OUTPUT

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

00:00.01 |               16|             |           |              |

 

|   3|    INDEX FULL SCAN               | IDX_T1_ID |             1 |    100 |         100|00:

00:00.01 |               8 |             |           |              |

 

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

00:00.06 |    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):

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

 

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

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

23 rows selected.

                  对连接条件列建索引，可以消除一张表的排序，提升效率。

                  此外还可以增大内存排序区，避免在排序尺寸过大时在磁盘中排序。