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 the other table to find, no index is generally not nested loops. Generally in the nested loop, the driver table satisfies the condition result set is not big, the driver table's connection field must have the index, thus goes nstedloop. If the driver table returns too many records, it is not suitable for nested loops. If the connection field does not have an index, it is appropriate to walk the hash join because no index is required.

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.

Key points are as follows:
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 perform nested loop joins
3) Nested loop is generally used for indexes in connected tables, and when index selectivity is good
4) The order of the oin is 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 one table, accessing another table (usually an index) to make a match, Nested loops the application is more efficient when an association table is smaller.

2. HASH JOIN

The hash join is a common way for the CBO to connect in large datasets. The optimizer scans the small table (the data source), uses the connection key (that is, calculates the hash value according to the connection field), builds the hash table in memory, then scans the large table, detects the hash table once every record is read, and finds the row that matches the hash table.

When a small table can be put all in memory, its cost is close to the sum of the cost of a full table scan of two tables. If the table is large and cannot be completely put into memory, then the optimizer splits it into several different partitions, which cannot be put into the memory section to write the partition to a temporary segment of the disk, with a large temporary segment to maximize I/O performance. Partitions in a temporary segment 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 partitioning of two tables, the benefit is that you can use parallel query, which is where multiple processes join and then merge with different partitions. But complex.

When using HASH join, the Hash_area_size initialization parameters must be large enough, if 9i,oracle is recommended to use SQL Workspace Auto-management, set Workarea_size_policy to Auto, and then adjust Pga_aggregate _target can be.

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.

Key points are as follows:
1) Hash joins are a common way for the CBO to make large data set connections.
2) You can also use the Use_hash (table_name1 table_name2) hint to force a hash connection
3) Hash join in two tables when the amount of data varies greatly.
4) A hash join works by hashing a table (usually a smaller table) and storing it in a hash list, extracting records from another table, doing hash operations, and finding the corresponding values in the hash list to match.

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

3. SORT MERGE JOIN

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

The sort merge Join performance cost is almost always in the first two steps. Generally, in the absence of an index, 9i began to appear very rarely, because its sorting cost is high, mostly for the hash join substitution.
The hash join generally works better than the sort merge join, but if the row source is already queued and does not need to be reordered when the sort merge join is executed, then 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 the table access through ROWID.

Key points are as follows:
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) Typically, this type of join is used only when the following conditions occur:
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, then extracts the data from the respective sort table and makes the matching in the other sort table, because the merge join needs to do more sorting, so it consumes more resources.

f) like, don't like
In general, where you can use the merge join, the hash join can perform better

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