Oracle 使用笔记

Nested Loop Join,  Hash Join， Merge Join

Nested Loop Join

原理： 

For Each Row R_A in Table_A (outer table)
    For Each Row R_B in Table_B (inner table)
        IF R_A join with R_B Then
          Return (R_A, R_B)

适用范围：

a.  outer table 很小； or

b.  inner table 在join条件上有index.

Merge Join

原理： 

Get First Row T1R from Table1
Get First Row T2R from Table2

While Not End of Table1 and Not End of Table2
  If T1R joins with T2R
      Get Next Row from Table2
      Returns (T1R, T2R)
  Else T1R < T2R
      Get NEXT Row from Table1
  Else
      Get Next Row from Table2
End Loop

适用范围：

a.  Table 1 和 Table2 都按Join Key排好序；

Hash Join

原理：

Partion Table1 into several buckets PA_1, PA_2, ..., PA_n by hash key;
Partion Table1 into several buckets PB_1, PB_2, ..., PB_n by hash key;
For i = 1 to n
     Join PA_i with  PB_i and get Joined result as J_iMerge J_i and retun

适用范围：

a. 表的尺寸较大；

b. DB的CPU和内存充足。

MATERIALIZE HINT

描述：指示优化器将内联视图实体化————执行过程中会创建基于视图的临时表。

好处： 相同子查询可多次被使用