Oracle table three ways to connect (SQL optimized)

Go from Network

1. NESTED LOOP

The nested loop connection is a good choice for cases where the subset of data being connected is small. Nested loop is to scan a table, each read to a record, according to the index to go to a table to find, no index is generally not nested loops. Generally in nested loop, the driver table satisfies the condition result set, and the connected field of the driver table is indexed. So we go nstedloop. Assuming that the driver table returns too many records, it is not suitable for nested loops. Suppose the join field is not indexed. It is suitable for a hash join because it does not need to be indexed.

You can use the ordered hint to change the default driver table of the CBO, using the USE_NL (table_name1 table_name2) hint to force the nested loop.

Highlights such as the following:
1) Nested loops are a good choice for smaller subsets of connected data
2) Use USE_NL (table_name1 table_name2) but force CBO to run nested loops join
3) Nested loop is typically indexed in a connected table. And when the index selectivity is good,
4) The order of oin is very important. The record set of the driver table must be small, and the response time of the returned result set is the fastest.
5) Nested loops works by reading data from a table and visiting a table (usually an index) to make a match. Nested loops is useful when a relational table is relatively small, and the efficiency is higher.

2. HASH JOIN

A hash join is a common way to use the CBO when it joins large datasets.

The optimizer scans the small table (the data source), builds the hash table in memory with the connection key (that is, calculates the hash value based on the connection field), and then scans the large table. Each time a record is read, the hash table is detected, and the rows matching the hash table are found.

When a small table can be all put into memory, its cost is close to the sum of the cost of a full table scan of two tables. Assuming that the table is very large can not be completely put into memory, the optimizer will cut it into a number of different partitions, can not be placed in the memory portion of the partition to write to the disk temporary segment, at this time to have a large temporary segment to maximize the performance of I/O. The partitions in the temporary section need to be swapped into memory for a hash join. At this time the cost is close to the full table Scan small table + partition number * Full table scan large table cost and.

As for the two tables are partitioned. The advantage is the ability to use parallel query, where multiple processes join at the same time for different partitions and then merge.

But complicated.

When using a HASH join, the Hash_area_size initialization parameters must be large enough, assuming that 9i,oracle is recommended to use the SQL workspace to proactively manage it, set Workarea_size_policy to Auto, and then adjust Pga_ Aggregate_target can do it.

Hash join may have advantages under the following conditions:
1) A connection between two huge tables.

2) A connection between a huge table and a small table.

Highlights such as the following:
1) hash joins are often used when the CBO is connected to large datasets.
2) can also use Use_hash (table_name1 table_name2) hint to force hash connection
3) Hash join is a very large difference in the amount of data in two tables.
4) The hash join works by hashing a table (which is usually a smaller table) and storing it in a hash list. Extract the records from a table and do hash operations. Find the corresponding value in the hash list. Make a match.

Use the ordered hint to change the default driver table for the CBO. You can use the Use_hash (table_name1 table_name2) hint to force a HASH join.

3. SORT MERGE JOIN

A) Make table access full for each of the connected tables;
b) Sort the results of table access full;
c) The merge join merges the sorting results.

The sort merge Join performance overhead is almost in the first two steps.

Usually, without an index, 9i starts to appear very rarely. Because of its high sorting cost, it is mostly a hash join substitution.
The hash join usually works better than the sort merge join. However, suppose that the row source has been ordered. You do not need to reorder when you run the sort merge join. The sort merge join will perform better than the hash join.
The sort merge join will perform better than nested loops when the full table scan is more preferable than the "index range scan and then rowid" table access.

Highlights such as the following:
1) Use Use_merge (table_name1 table_name2) to force the use of sort merge connections.
2) Sort Merge Join is used in cases where there is no index and the data is already sorted.
3) Connection step: Sort the two tables and merge the two tables.
4) Usually, only when the following conditions occur. Before you can use this join method:
A) Rbo mode
b) Not equivalent Association (>,<,>=,<=,<>)
c) Bhash_join_enabled=false
d) The data source is sorted
e) The merge join first sorts the associated columns of the associated tables, extracts the data from the respective sort tables, and matches them in a sort table, because the merge join requires a lot of other sorting. So it consumes a lot of other resources.

f) like, don't like
In general, you can use the merge join place. Hash join can play a better performance

You can use the Use_merge (table_name1 table_name2) hint to force the sort MERGE join.

Oracle table three ways to connect (SQL optimized)