Shulou(Shulou.com)05/31 Report--

This article will explain in detail what table connections are in the database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1 nested loop join

Circular nested join: each record of row source 1 matches each record of row source 2 in turn, and the records that meet the connection criteria are placed in the result set until all records of row source 1 complete the operation. Circular nested join is the most basic and oldest form of table join.

2 sort merge join

Sort merge join: the data of row source 1 and row source 2 are sorted respectively, and then the two sorted source tables are merged, and the records that meet the join criteria are put into the result set. Because sorting requires memory space, sort merge join consumes a lot of memory. If memory space (8i is sort_area_size,9i or above uses PGA) is insufficient, temporary tablespaces will be used, which will reduce the efficiency of sorting merge joins. Sort merge join is one of the oldest table joins.

Attached is an explanation from tom's brother:

You Asked

Tom

What is the difference between "Sort Merge" and "Hash" Joins. Don't they both do an 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 An and B is sort of like this:

Read An 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_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 An 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

Hash join: row source 1 is calculated as a hash table based on the join key, and each record of row source 2 scans the hash table in turn to find a matching record and put it in the result set. Computing hash tables requires memory space, and hash join also consumes a lot of memory. If memory space (8i is hash_area_size,9i or above uses PGA) is insufficient, temporary tablespaces will be used, which will reduce the efficiency of hash joins.

This is the end of this article on "what are the table connections in the database?". I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.