Nesting loops for Oracle table joins

1. Single table access : sub-table, partition , build cable , full table scan---Open parallel, always put it in memory, compression

2. Multi-table Association, any time can only be 2 Table association, the resulting set of results and other tables are associated.

3. Nested loops: Oracle reads a row from a small result set (drive table/External table) and then compares all data in a large result set (the probed table/internal table) one by one (nested loops can be used for non-equivalent connections) and, if the rules are met, into the result set, The next piece of data for the smaller result set is then resumed until the end of the loop. Nested loops are only suitable for outputting a small number of result sets or for fast output result sets. is actually the equivalent of a double for loop.

Sql> select * from table (Dbms_xplan.display_cursor (null,null, ' ALLSTATS LAST ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------Sql_id bv300dy9b7gyn ,  child number 0-------------------------------------select /*+ first_rows */  e.ename,e.job,d.dname from emp e,dept d  wheree.deptno=d.deptno and  e.sal<2000plan hash value: 3625962092------------------------------------------------- ----------------------------------------------------------| id  | operation        | Name    | Starts | E-Rows |  a-rows |   a-time   | buffers | reads  |-----------------------------------------------------------------------------------------------------------|    0 | SELECT STATEMENT      |         |      1 |  | 7 |00:00:00.14  |      18 | 8 | |    1 |  NESTED LOOPS       |         |      1 |       4 | 7 |00:00:00.14 |      18 | 8  ||    2 |   NESTED LOOPS       |         |      1 |       4 | 7 |00:00:00.14 |      11 | 7 | | *  3 |    table access full      |  EMP     |      1 |       4 | 7 |00:00:00.12 |       7 | 6  | | *  4 |    index unique scan      |  pk_dept |      7 |      1 |  7 |00:00:00.01 |       4 | 1 | |    5 |   TABLE ACCESS BY INDEX ROWID| DEPT     |      7 |      1 |  7 |00:00:00.01 |       7 | 1 |--------------------------------------------------------------------------------------------------------- --predicate information  (identified by operation id):-------------------------------- -------------------   3 - filter ("E". " SAL "<2000"    4 - access ("E". ") DEPTNO "=" D "." DEPTNO ") 24 rows selected.

Near the keyword is the driver table, nested loops of rows is wrong, nested loops of the algorithm, such as a NL B, such as a table has 1000, from a table to take 1000 data, scan A, the 1000 data to B, and then B is scanned 1000 times, So where is the data from the A-table taken out? Match one and then immediately return, NL does not need the PGA, because no cache data, if the multi-layer NL, is still the PGA, multi-layer NL easy to cause CBC,

Nested loops, filtered back to the result set of small when the driver table, outer joins, nested loops can not modify the driver table, in the nested loop, the driver table of the connection column must have an index, from the above execution plan can see the deptno value of E to D table deptno, the driver table of the Connection column without index,

NL must be the driver table return data amount of time only to go, in the SQL statement has count,group by,distinct,sum and other keywords, can not go nl, if the OLTP system, there are a lot of DISTINCT, can only explain the table design problems, with the intermediate table to all the association to re-solve the distinct,

If there is a lot of NL in the execution plan, starting from the innermost, if the innermost error, then the outside of NL all wrong, from the inside out constantly see NL.

How can I tell if NL is right? 1, look at the amount of data returned by the driver table, 2, see if the drive table is going to index, 3. See how many result sets are eventually returned. So the 3rd article is the most important. How many result sets will eventually be returned determines whether to go NL or hash.

If a NL B, return 10w data, if a:b=1:1, then a at least return 10w data, then B is scanned 10w times, if A:b=1:10, then a at least to return 1w data, then B is scanned 1w times, B table Go index, B table Go index, table 10 data, Then the total number of Table B table is 10w, so in NL, the drive table no matter how many times, then the return table count is the final return data bar number, so the nested loop is not suitable for large amounts of data, the root cause is to return to the table or back to the table again filtered, if not back to the table or back to filter, then NL

The connection column of the driver table is very high, if the cardinality is very low, can not go NL, such as 1:1w,1:n, then N too large

NL requires only SGA, does not require PGA,NL to support non-equivalent jion, and hash join supports only equivalent associations.

Determine whether to go NL and hash, according to the final return of the result set to judge, followed by the driver table returns the number of rows, and then the driver table of the Jion column cardinality.

Error nl,1. A single return of large amounts of data, such as 100w

Nesting loops for Oracle table joins