數(shù)據(jù)庫中表連接方式有哪些

這篇文章將為大家詳細(xì)講解有關(guān)數(shù)據(jù)庫中表連接方式有哪些，小編覺得挺實(shí)用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

1 nested loop join

循環(huán)嵌套連接：行源1的每一條記錄，依次去匹配行源2的每條記錄，將符合連接條件的記錄放在結(jié)果集中，直到行源1的所有記錄都完成這個操作。循環(huán)嵌套連接是最基本也是最古老的表連接方式。

2 sort merge join

排序合并連接：行源1和行源2的數(shù)據(jù)分別排序，然后將兩個排序的源表合并，符合連接條件的記錄放到結(jié)果集中。由于排序需要內(nèi)存空間，sort merge join對內(nèi)存有比較大的消耗，如果內(nèi)存空間(8i為sort_area_size，9i及以上使用PGA)不足，則會使用臨時表空間，這樣會降低排序合并連接的效率。排序合并連接是最古老的表連接方式之一。

附上tom哥哥的解釋：

You Asked 
Tom,

What is the difference between "Sort Merge" and "Hash" Joins. Don't they both do a one
FULL scan each on the joining tables and join them?

I know Sort Merge is used in the case of "ALL ROWS" and Nested Loops in the case of
"FIRST ROWS" hints. How about Has Join? When is it used?

Would really appreciate if you could explain it with a couple of examples.

Thanks in advance. 


and we said...
Well, a sort merge of A and B is sort of like this:

   read A and sort by join key to temp_a
   read B and sort by join key to temp_b

   read a record from temp_a
   read a record from temp_b
   while NOT eof on temp_a and temp_b
   loop
        if ( temp_a.key = temp_b.key ) then output joined record
        elsif ( temp_a.key <= temp_b.key ) read a record from temp_a
        elsif ( temp_a.key >= temp_b.key ) read a record from temp_b )
   end loop

(its more complex then that, the above logic assumed the join key was unique -- we really
need to join every match in temp_a to every match in temp_b but you get the picture)

The hash join is conceptually like:

   create a hash table on one of A or B (say A) on the join key creating temp_a.

   while NOT eof on B
      read a record in b
      hash the join key and look up into temp_a by that hash key for matching 
      records
      output the matches
   end loop

So, a hash join can sometimes be much more efficient (one hash, not two sorts)

Hash joins are used any time a sort merge might be used in most cases.  If you don't see
hash joins going on, perhaps you have hash_join_enabled turned off...

3  hash join

哈希連接：將行源1計算成一張基于連接鍵的hash表，行源2的每條記錄依次掃描這張hash表，找到匹配的記錄放到結(jié)果集。計算hash表需要內(nèi)存空間，hash join同樣對于內(nèi)存有比較大的消耗，如果內(nèi)存空間(8i為hash_area_size，9i及以上使用PGA)不足，則會使用臨時表空間，這樣會降低哈希連接的效率。

關(guān)于“數(shù)據(jù)庫中表連接方式有哪些”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學(xué)到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。