Plsql_ Performance Optimization Series 02_oracle Join Association

2014-09-25 Baoxinjian

I. Summary

Comparison of three primary connectivity methods for Oracle

1. Hash Join

(1). Overview

I. Read data from a table and place it into memory and create a bitmap index of the unique keyword

Ii. reading another table, and comparing the in-memory table with the hash algorithm

(2). Applicable objects

I. Large Table Connection Small table

Ii. two large tables

2. Nested Loops

(1). Overview

I. Circular appearance record

II. Whether the connection between the individual alignment and the internal standard meets the conditions

(2). Applicable objects

Small tables drive large tables, returning fewer result sets

3. Merge Join

(1). Overview

I. Two tables for table access full

Ii. sorting the results of table access full

Iii. merging the sorting structure with a merge join

(2). Applicable objects

Accessing Data through ROWID

When SQL accesses multiple tables, the association has an important impact on SQL efficiency. The association takes into account two factors, the type of join, and the order of join.

Second, join classification

1. Nested Loop Join method

1.1 Applicable conditions

(1). Associate a small amount of data (rows), the return set is small.

(2). The associated condition can efficiently access the second table (inner table). Efficient access is associated with conditions such as ' = ', whereas non-efficient association conditions such as '! = ', ' > ', etc. are indexed on inner table (that is, non-driver).

Therefore, it is more suitable for OLTP systems because of the small amount of data returned in OLTP systems and the higher indexes on the table.

1.2 Implementation steps

(1). Optimizer Select Driver table (Driving table), specify it as outer table

(2). Specify another table as inner table (not a driver)

(3). Access inner table According to the associated field for each row of outer table. As shown below:

NESTED LOOPS

Outer_loop

Inner_loop

Because nested loop queries from outer table to inner table, the Order of association is more important.

Example of 1.3 nexted Loop Join

2. Hash Join method

2.1 Applicable conditions

(1). Only for equivalent associative equijoin (e.g. =);

(2). Any one of the following conditions is true:

Large Table Association

Or most records of a small table participate in the Association

2.2 Implementation Mechanism

(1). The optimizer chooses a smaller table and constructs a hash table based on the join key. (Driver table)

(2). Scan another larger table and search for the associated row in the hash table

If there is enough memory, the small table is all in memory, this is optimal, and the cost can be estimated to be two tables read all the time.

If there is not enough memory, a portion of the small table can be placed in temporary tablespace (the temp table space should be large enough) to maximize the IO speed.

Example of 2.3 Hash join

3. Sort Merge Join

3.1 Where applicable

Hash join typically performs better, but if the associated data is sorted or not ordered, the sort merge join performs better.

Non-equivalent associations (Nonequi joins, such as <,>) are useful because the sort merge join is better than nested loop when the return set is large, and the hash join can only be used in equijoin.

3.2 Implementation Mechanism

(1). Sort action: The associated data is sorted by the associated field. This action is not required if the data is originally sorted

(2). Merge operation: sorted data for the merge operation.

It is necessary to note that the sort merge join does not have the concept of driving table

4. Descartes Connection

No associated conditions should be avoided as far as possible.

5. Outer Join

5.1 Simple Join Extension

Customers table called preserved table,orders table called optional

5.2 Extension of Outer join

• Left OUTER JOIN
• Right outer join
• Full OUTER JOIN

5.3 Outer join can also be nested loop, hash join, sort merge, and so on compared to normal join. But there are some differences:

1. Nested Loop Outer Join, use preserved table as the driver table instead of selecting the driver table as normal join based on cost.

2. Full Outer Join (equijoin) in 11g, automatically using the algorithm based on the hash Join. Hash JOIN full OUTER appears in execution plan.

You can use Hint:native_full_outer_join/no_native_full_outer_join to specify or not to use this algorithm.

If not used, the execution plan for the full Outer Jion is the union of the left Outer join and right Outer jion.

Third, join order

The basic principle is to record fewer first associations so that fewer records are involved in subsequent associations. Specifically:

(1). Select the table that can exclude the most records as driving table

(2). In the remaining tables, select the table that has the best filter (excluding the most records) as the first table to participate in the association

(3). etc.

See this example:

SELECT Info
From Taba A, Tabb B, tabc C
WHERE A.acol between and 200
and B.bcol between 10000 and 20000
and C.ccol between 10000 and 20000
and a.key1 = B.key1
and A.key2 = C.key2;

It is assumed that a table has the lowest record after filter, b followed, and C records most. Then you can use a as driving table, first associated with B, and finally with C

IV. using hint to select the association mode and order

1. Use hint to specify how to associate

The Oracle optimizer automatically chooses the way to join, but sometimes it is not optimal, and developers can use hint to select the Join method and compare execution efficiency.

The relevant hint are:

• Use_nl,use_hash,use_merge
• In the EXISTS clause, HASH_SJ,MERGE_SJ,NL_SJ
• In clause, Hash_aj,merge_aj,nl_aj

2. Use hint to specify the association order

If the association order selected by the Oracle optimizer is not what you want, you can specify it with hint (leading and ordered). Ordered represents the order in which the table appears in the SQL statement, leading can be arbitrarily specified and more general.

Leading Specifies the selected order of driving table. (in nested loop, driving table is outer table, and in the hash join, it is a hash table.) ）

SELECT/*+ Leading (a b c) */info

WHERE A.acol between and 200

and B.bcol between 10000 and 20000

and C.ccol between 10000 and 20000

and a.key1 = B.key1

and A.key2 = C.key2;

3. Undocumented hint parameter: swap_join_inputs

Note that in the example above, a as a driver table and a B association, the association results as the driver table, and then the C Association. Sometimes you need to change the order, as in the following example

SELECT/*+ Leading (a b c) */info

WHERE a.key1 = B.key1

and B.key2 = C.key2;

If a 1000, b 100,000, C 10,000. Because the A and C tables have no associated fields, A and B are associated first, and then C. But if a correlation B produces 20,000 records, and C is associated, I want to use C as the driver table, can it be implemented?

The Hash_join can be implemented with the implicit hint parameters of Oracle Swap_join_inputs:

SELECT/*+ Leading (a b c) swap_join_inputs (c) */info

WHERE a.key1 = B.key1

and B.key2 = C.key2;

Abalone New *******************

Reference: http://blog.itpub.net/18474/viewspace-1060728/

Reference: metalink:how to switch the driving table in a hash join [ID 171940.1]

Plsql_ Performance Optimization Series 02_oracle Join Association