12 Sort Merge joins (sort merge Join)--optimization Theme Series

SELECT * from A,b where a.id<=b.id; this general go sort Merge Join

Sort Merge Join (Sortmerge join)

The preceding article mentions nested loops and hash joins, and they all have a driver table concept. Sort Merge Join no driver table one says that two table/row sources are peer-to-peer relationships. The sort merge Join principle is to sort the two table/row sources according to the join column (and, of course, to kick out a column that does not conform to the where condition) before connecting. Sort merge joins can handle non-equivalent joins. Sometimes there is a mergejoin join, and you have to go to sort. Based on the principle of sort merging, we know that sort merge joins are actually very resource-intensive, because 2 tables/result sets are sorted, so the CBO will normally not choose to go sortmerge join unless the following conditions are:

Sort Merge Connection It's a garbage. Below explain

SELECT * from A, b where a.id>b.id;

A 1000W Line

B 500W Line

1. Use of hint

2. Non-equivalent Join

3. There is a table or 2 tables have been sorted well

4.JOIN indexed, Indexfull SCAN (please review the access path of the previous article) returns an ordered result and joins with another table.

INDEX full SCAN----TABLE ACCESS by INDEX ROWID

This is not only suitable for table data very little

If there's a 1000W line,

If you go indexfull SCAN----TABLE ACCESS by INDEX ROWID

Index full scan+table ACCESS by INDEX ROWID If the assembly is 50 times times slower than the whole table + sort

Here's an example (based on SCOTT,ORACLE11GR2):

Alter session set Statistics_level=all;

Select ename, Job, Sal, Dname, loc from emp, dept Whereemp.deptno = Dept.deptno;

SELECT * Fromtable (Dbms_xplan.display_cursor (Null,null, ' allstats last '));

Is the NL loop going to be physically IO?

Sort merge joins to compare in memory

Sort Merge Joins

In fact, to return the small amount of data on the above

Roles similar to driver tables

Sort merge joins why garbage.

1. Sort and 2 sides are sorted

2. A sort of NL-like algorithm after sorting

is sorting the merge going to cost the PGA?

The two tables were accessed several times. id=2 3 is sorted according to which column. Deptno

id=2 3, how many records are returned, 4 bars, right?

How many times has id=4 been executed here? 4 times.

Do I now have 2 result sets that have been sorted ...

The first sort is not returning 4 results ...

The sort lookup algorithm takes a result compared to the other already sorted results, and 4 results are compared 4 times.

Is this algorithm similar to NL?

Sort merge joins are garbage sorted and also like NL operations

Let's say that the result of a large order of two tables is too large for a 100W record to look for 100W times.

After sorting, we're going to do a NL-like algorithm. This time CPU decisive tragedy

But this time the NL is going to be better than the real NL. Because it's in memory.

Sort merge joins generally do not see little use of data quantity less go sort merge Join no problem data volume Big CPU easy 100% PGA also full

So why do you sometimes have to go sort merge joins?

Because the condition is not equal to not go hash join more can't go NL because NL is not in memory to search the need to consume disk I/O

But the sort merge connection is consuming CPU generally speaking I/O consumption is more serious than CPU consumption