In -depth understanding of Oracle Tables (3): Three table connection methods nested loop Join and Sort merge JoinCategory: Oracle Foundation Management Oracle SQL Development2013-01-28 00:332536 People readComments (1) CollectionReport
offThe essence of database technology is to standardize data storage through relational tables.
Information retrieval and processing are carried out through various table connection techniques and various types of indexing techniques.
Here think is willing to learn to share with you the three major table joining technologies of Oracle
In earlier versions, Oracle provided a NESTED-LOOP join, with two table joins equivalent to a double loop, assuming that the two tables had m and n rows respectively
If the inner loop is a full table scan, the time complexity is O (m*n)
If the inner loop is an index scan, the complexity of the time is O (m*㏒n)
And the time complexity of the hash join is O (m*n)
Therefore, after 10g, the hash join becomes the default connection method
For three kinds of connections, we can use hint to force the optimizer to go: Use_hash,use_nl,use_merge
The outline of the three major joining methods is listed as follows:
Nested loop
Draw a record from a table, traverse the B table to find the matching record, and then draw a line from a to traverse the B table ...
is a double cycle.
Hash join
The a table by the connection key to calculate a hash table, and then extract the records from the B table, calculate the hash value, according to hash to a table hash to match the criteria of the record
Sort Merge Join
Arrange the list of a, B, and then merge to match the criteria.
Next talk about the various connections
㈠nested Loop Join
⑴ principle of execution
For example:
Select t1.*,t2.* from T1,t2 where t1.col1=t2.col2;
The access mechanism is as follows:
For I in (SELECT * from T1) loop
For j in (SELECT * from T2 where col2=i.col1) loop
Display results;
End Loop;
End Loop;
Similar to a nested loop
When a nested loop executes, the outer loop enters the inner loop, and after the inner loop terminates
The outer loop is then executed by the outer loop into the inner loop, and when the outer loop is all terminated, the program ends
The ⑵ steps are as follows:
① Determining the driver table
② Assigning the inner table to the driver table
③ access to all rows of the driver table for each row of the driver table
The ⑶ implementation plan is broadly as follows:
NESTED LOOPS
Outer_loop
Inner_loop
When the optimizer mode is first_rows, we often find a large number of nested loops
At this point, we do not need to cache any data when returning data to the user, which is a highlight of nested loop
⑷ Usage Scenarios
Generally used in linked tables with indexes, and good index selectivity (that is, selectivity close to 1)
That is, the record set of the driver table is relatively small (<10000) and the inner table requires a valid access method (Index)
It should be noted that the order of joins is important, the record set of the driver table must be small, and the response time of the returned result set is the fastest
the relationship between ⑸ and indexes
Nesting loops and indexes like twins, common considerations and design
This can be seen from the optimizer's execution mechanism, such as the existence of 2 tables, a 10 record, and a 10 million record
With a small table as the driver table, the cost is: 10* (the cost of querying a record in a large table by index)
If 10 million of the big table is not indexed, then cost is conceivable
Therefore, when connecting to multiple tables, be aware that the connected fields of the driver table need to create an index
or whether you need to create a composite index on the join field and other constraint fields on the table
㈡sort Merge Join
⑴ principle of execution
For example:
Select t1.*,t2.* from T1,t2 where t1.id=t2.id;
The access mechanism is as follows:
Access T1, and order by T1_1.id, where the ID represents the connection field
Access T2, and order by t2_1.id
Join t1_1.id = t2_1.id, alternating in turn to merge, but does not matter drive
⑵ Usage Scenarios
Although, hash join is used to replace SJ, but if your server CPU resources and mem resources are very tense, it is recommended to use the sort MERGE JOIN
Because the hash join requires more resources than the sort merge join. Especially CPU
The 10g SQL Tuning document reads:
On the other hand, Sort-merge joins can perform better than hash joins if both of the following conditions is met:
The row sources is already sorted.
A sort operation does not has A to is done.
So, SJ is probably used in cases where there are no indexes and the data is sorted
Because the hash join is more important and difficult to understand, so here think alone for it to open the next blog inside
Source: >
From for notes (Wiz)
In-depth understanding of Oracle Tables (3): Three table connection methods nested loop Join and Sort Merge Join
