Oracle table Connection Methods

Source: Internet
Author: User

When viewing the SQL Execution Plan, we will find that there are many table connection methods. This article introduces the table connection methods to better understand the execution plan and understand the SQL Execution principles.

I. Connection method:

Nested loop (Nested Loops (NL ))

(Hash) Hash Join (HJ ))

(Merge) sortingMerge connections(Sort Merge Join (SMJ )) 

Ii. Connection description:

1. Oracle can only connect two tables at a time. No matter how many tables are in the query, Oracle can only operate on two tables at a time during a connection.

2. when multiple tables are connected, the optimizer starts from one table and connects it to another table. Then, the intermediate result is connected to the next table, and so on, until all tables are processed. 

Iii. Table join details:

1. Nested loop (Nested Loops (NL )):

Nested loop Implementation Mechanism (pseudo code ):

For r1 in (select rows from table_1 where colx = {value })

Loop

For r2 in (select rows from table_2 that match current row from table_1)

Loop

Output values from current row of table_1 and current row of table_2;

End loop;

End loop;

This Code consists of two loops.

These two tables in a nested loop are usually calledExternal table and inner table ).

In nested loop join, an External table is also calledDriver table(Driver table)

In pseudocode, table_1 is the driving table, and table_2 is the internal table.

From the pseudo code, we can see that the connection process is2Layer nested loops, so the less the number of Outer Loops, the better. That is why we set small tables or return smallResult setAs the reason for driving the table.

Nest loop join cost = COST of getting data from the first table + COST of accessing the second table from the base worker of the result obtained from the first table. Therefore, nested loops are generally suitable for driving the table record set. relatively small (<10000) efficient index access is available for internal tables. Use USE_NL (table_1 table_2) to force CBO to execute nested loop connections. Determine the driver table: select rows from table_1 where colx = {value} is generally a table that can obtain a small result set based on the where condition, not necessarily a table with relatively small records.

2. Hash Join (HJ )):

Hash joinIt is generally used for a small table and a large table.Join.In most cases,Hash joinEfficiencyJoinHigher efficiency. 

3. SortingMerge connections(Sort Merge Join (SMJ )):

In general, the effect of the hash join is better than that of the sort merge join. However, if the row source has already been sorted, you do not need to sort it when executing the sort merge join, in this case, the performance of sorting and merging connections is better than that of hash connections. You can use USE_MERGE (table_1 table_2) to force sort and merge connections.

Process: sort two tables, and then merge the last two tables.

  • 1
  • 2
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.