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

This article mainly shows you "how to use hash join and nested loop in Oracle database", the content is easy to understand, clear, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "how to use hash join and nested loop in Oracle database" this article.

The connection mode of Oracle table-Nested loop join and Sort merge join

The essence of relational database technology is to standardize data storage through relational tables, and to retrieve and process information through various table join technologies and various types of index technologies.

There are three ways to associate a table:

Nested loop: take a record from table A, traverse table B to find a matching record, and then extract an entry from table a to traverse table B. It is a double loop hash join: calculate a hash table from the A table by the join key, then extract the records from the B table one by one, calculate the hash value, match the qualified records according to the hash from hash to A table sort merge join: arrange all the sort merge join B tables in order, then do merge, and select them according to the conditions.

For all three connections, we can use hint to force the optimizer to go: use_hash,use_nl,use_merge.

Nested Loop Join

1. Executive principle

For example: select t1.handwriting t2.* from T1 select T2 where t1.col1traint2.col2; the access mechanism is as follows: for i in (select * from T1) loop-T1 is the driver table for j in (select * from T2 where col2=i.col1) loop display results; end loop; end loop Similar to the execution of a nested loop, the outer loop first enters the inner loop, and then executes the outer loop after the inner loop terminates, and then enters the inner loop from the outer loop. When the outer loop is all terminated, the program ends.

two。 The steps are as follows

a. Determine the driver table b. Assign inner table to driver table c. Access all rows of the driven table for each row of the driven table

3. The execution plan is roughly as follows

NESTED LOOPSouter_loop-when the inner_loop optimizer schema of the driver table is FIRST_ROWS, we often find that there are a large number of NESTED LOOP. When we return the data to the user, there is no need to cache any data, which is a highlight of nested loop.

4. Working with scen

It is generally used to have indexes in joined tables, and when the index selectivity is good (that is, Selectivity is close to 1), that is, the recordset that drives the table is relatively small (

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.