oracle 表连接方式

 Nested Loop Joins

Nested loop joins are useful when the following conditions are true:

• The database joins small subsets of data.

• The join condition is an efficient method of accessing the second table.

It is important to ensure that the inner table is driven from (dependent on) the outer table. If the inner table's access path is independent of the outer table, then the same rows are retrieved for every iteration of the outer loop, degrading performance considerably. In such cases, hash joins joining the two independent row sources perform better.

A nested loop join involves the following steps:

1. The optimizer determines the driving table and designates it as the outer table.

2. The other table is designated as the inner table.

3. For every row in the outer table, Oracle Database accesses all the rows in the inner table. The outer loop is for every row in the outer table and the inner loop is for every row in the inner table. The outer loop appears before the inner loop in the execution plan, as follows:

   NESTED LOOPS   outer_loop   inner_loop 

See Also:

"Cartesian Joins"

  Original and New Implementation for Nested Loop Joins

Oracle Database 11g introduces a new implementation for nested loop joins. As a result, execution plans that include nested loops might appear different than they did in previous releases of Oracle Database. Both the new implementation and the original implementation for nested loop joins are possible in Oracle Database 11g. So, when analyzing execution plans, it is important to understand that the number of NESTED LOOPS join row sources might be different.

  Original Implementation for Nested Loop Joins

Consider the following query:

SELECT e.first_name, e.last_name, e.salary, d.department_name    FROM hr.employees e, hr.departments d    WHERE d.department_name IN ('Marketing', 'Sales')      AND e.department_id = d.department_id;

Before Oracle Database 11g, the execution plan for this query might appear similar to the following execution plan:

-------------------------------------------------------------------------------------------------| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |                   |    19 |   722 |     3  (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1  (0)| 00:00:01 ||   2 |   NESTED LOOPS              |                   |    19 |   722 |     3  (0)| 00:00:01 ||*  3 |    TABLE ACCESS FULL        | DEPARTMENTS       |     2 |    32 |     2  (0)| 00:00:01 ||*  4 |    INDEX RANGE SCAN         | EMP_DEPARTMENT_IX |    10 |       |     0  (0)| 00:00:01 |------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

In this example, the outer side of the join consists of a scan of the hr.departments table that returns the rows that match the condition department_name IN('Marketing', 'Sales'). The inner loop retrieves the employees in the hr.employees table that are associated with those departments.

  New Implementation for Nested Loop Joins

Oracle Database 11g introduces a new implementation for nested loop joins to reduce overall latency for physical I/O. When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. Oracle Database 11g can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time.

As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join.

Consider the query in "Original Implementation for Nested Loop Joins". In Oracle Database 11g, with the new implementation for nested loop joins, the execution plan for this query might appear similar to the following execution plan:

------------------------------------------------------------------------------------------------| Id  | Operation                    | Name              | Rows  | Bytes | Cost(%CPU)| Time      |-------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |                   |    19 |   722 |     3   (0)| 00:00:01 ||   1 |  NESTED LOOPS                |                   |       |       |            |          ||   2 |   NESTED LOOPS               |                   |    19 |   722 |     3   (0)| 00:00:01 ||*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     2 |    32 |     2   (0)| 00:00:01 ||*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 ||   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

In this case, the rows from the hr.departments table constitute the outer side of the first join. The inner side of the first join is the index emp_department_ix. The results of the first join constitute the outer side of the second join, which has the hr.employees table as its inner side.

There are cases where a second join row source is not allocated, and the execution plan looks the same as it did in prior releases. The following list describes such cases:

• All of the columns needed from the inner side of the join are present in the index, and there is no table access required. In this case, Oracle Database allocates only one join row source.

• The order of the rows returned might be different than it was in previous releases. Hence, when Oracle Database tries to preserve a specific ordering of the rows, for example to eliminate the need for an ORDER BY sort, Oracle Database might use the original implementation for nested loop joins.

• The OPTIMIZER_FEATURES_ENABLE initialization parameter is set to a release before Oracle Database 11g. In this case, Oracle Database uses the original implementation for nested loop joins.

  When the Optimizer Uses Nested Loop Joins

The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.

The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can serve as a row source for another nested loop join.

The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. If the access path for the inner loop is not dependent on the outer loop, then you can end up with a Cartesian product; for every iteration of the outer loop, the inner loop produces the same set of rows. Therefore, you should use other join methods when two independent row sources are joined together.

Nested Loop Join Hints

If the optimizer chooses to use some other join method, then you can use the USE_NL(table1 table2) hint, where table1 and table2 are the aliases of the tables being joined.

For some SQL examples, the data is small enough for the optimizer to prefer full table scans and use hash joins. This is the case for the SQL example shown in Example 11-8, "Hash Joins". However, you can add a USE_NL to instruct the optimizer to change the join method to nested loop. For more information on theUSE_NL hint, see "Hints for Join Operations".

 Nesting Nested Loops

The outer loop of a nested loop can be a nested loop itself. You can nest two or more outer loops to join as many tables as needed. Each loop is a data access method, as follows:

SELECT STATEMENT NESTED LOOP 3  NESTED LOOP 2          (OUTER LOOP 3.1)   NESTED LOOP 1         (OUTER LOOP 2.1)    OUTER LOOP 1.1     - #1    INNER LOOP 1.2     - #2   INNER LOOP 2.2      - #3  INNER LOOP 3.2       - #4

 Hash Joins

The database uses hash joins to join large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then scans the larger table, probing the hash table to find the joined rows.

This method is best when the smaller table fits in available memory. The cost is then limited to a single read pass over the data for the two tables.

  When the Optimizer Uses Hash Joins

The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:

• A large amount of data must be joined.

• A large fraction of a small table must be joined.

In Example 11-8, the database uses the table orders to build the hash table. The database scans the larger order_items later.

Example 11-8 Hash Joins

SELECT o.customer_id, l.unit_price * l.quantity  FROM orders o ,order_items l WHERE l.order_id = o.order_id;--------------------------------------------------------------------------| Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|--------------------------------------------------------------------------|   0 | SELECT STATEMENT     |              |   665 | 13300 |     8  (25)||*  1 |  HASH JOIN           |              |   665 | 13300 |     8  (25)||   2 |   TABLE ACCESS FULL  | ORDERS       |   105 |   840 |     4  (25)||   3 |   TABLE ACCESS FULL  | ORDER_ITEMS  |   665 |  7980 |     4  (25)|--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("L"."ORDER_ID"="O"."ORDER_ID")

  Hash Join Hints

Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables together. See "PGA Memory Management" to learn how to size SQL work areas. See "Hints for Join Operations" to learn about the USE_HASH hint.

  Sort Merge Joins

Sort merge joins can join rows from two independent sources. Hash joins generally perform better than sort merge joins. However, sort merge joins can perform better than hash joins if both of the following conditions exist:

• The row sources are sorted already.

• A sort operation does not have to be done.

However, if a sort merge join involves choosing a slower access method (an index scan as opposed to a full table scan), then the benefit of using a sort merge might be lost.

Sort merge joins are useful when the join condition between two tables is an inequality condition such as <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins unless there is an equality condition.

In a merge join, there is no concept of a driving table. The join consists of two steps:

1. Sort join operation: Both the inputs are sorted on the join key.

2. Merge join operation: The sorted lists are merged together.

If the input is sorted by the join column, then a sort join operation is not performed for that row source. However, a sort merge join always creates a positionable sort buffer for the right side of the join so that it can seek back to the last match in the case where duplicate join key values come out of the left side of the join.

 When the Optimizer Uses Sort Merge Joins

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:

• The join condition between two tables is not an equijoin.

• Because of sorts required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.

 Sort Merge Join Hints

To instruct the optimizer to use a sort merge join, apply the USE_MERGE hint. You might also need to give hints to force an access path.

There are situations where it makes sense to override the optimizer with the USE_MERGE hint. For example, the optimizer can choose a full scan on a table and avoid a sort operation in a query. However, there is an increased cost because a large table is accessed through an index and single block reads, as opposed to faster access through a full table scan.

For more information on the USE_MERGE hint, see "Hints for Join Operations".

  Cartesian Joins

The database uses a Cartesian join when one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with every row from the other data source, creating the Cartesian product of the two sets.

 When the Optimizer Uses Cartesian Joins

The optimizer uses Cartesian joins when it is asked to join two tables with no join conditions. In some cases, a common filter condition between the two tables could be picked up by the optimizer as a possible join condition. In other cases, the optimizer may decide to generate a Cartesian product of two very small tables that are both joined to the same large table.

 Cartesian Join Hints

Applying the ORDERED hint, instructs the optimizer to use a Cartesian join. By specifying a table before its join table is specified, the optimizer does a Cartesian join.

 Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

  Nested Loop Outer Joins

The database uses this operation to loop through an outer join between two tables. The outer join returns the outer (preserved) table rows, even when no corresponding rows are in the inner (optional) table.

In a regular outer join, the optimizer chooses the order of tables (driving and driven) based on the cost. However, in a nested loop outer join, the join condition determines the order of tables. The database uses the outer table, with rows that are being preserved, to drive to the inner table.

The optimizer uses nested loop joins to process an outer join in the following circumstances:

• It is possible to drive from the outer table to inner table.

• Data volume is low enough to make the nested loop method efficient.

For an example of a nested loop outer join, you can add the USE_NL hint to Example 11-9 to instruct the optimizer to use a nested loop. For example:

SELECT /*+ USE_NL(c o) */ cust_last_name, sum(nvl2(o.customer_id,0,1)) "Count"

 Hash Join Outer Joins

The optimizer uses hash joins for processing an outer join if the data volume is high enough to make the hash join method efficient or if it is not possible to drive from the outer table to inner table.

The order of tables is determined by the cost. The outer table, including preserved rows, may be used to build the hash table, or it may be used to probe one.

Example 11-9 shows a typical hash join outer join query. In this example, all the customers with credit limits greater than 1000 are queried. An outer join is needed so that you do not miss the customers who do not have any orders.

Example 11-9 Hash Join Outer Joins

SELECT cust_last_name, sum(nvl2(o.customer_id,0,1)) "Count"  FROM customers c, orders o WHERE c.credit_limit > 1000   AND c.customer_id = o.customer_id(+) GROUP BY cust_last_name;-----------------------------------------------------------------------------| Id  | Operation            |  Name           | Rows  | Bytes | Cost (%CPU)|-----------------------------------------------------------------------------|   0 | SELECT STATEMENT     |                 |   168 |  3192 |     6  (17)||   1 |  HASH GROUP BY       |                 |   168 |  3192 |     6  (17)||*  2 |   NESTED LOOPS OUTER |                 |   260 |  4940 |     5  (0) ||*  3 |    TABLE ACCESS FULL | CUSTOMERS       |   260 |  3900 |     5  (0) ||*  4 |    INDEX RANGE SCAN  | ORD_CUSTOMER_IX |   105 |   420 |     0  (0) |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("C"."CREDIT_LIMIT">1000)   4 - access("C"."CUSTOMER_ID"="0"."CUSTOMER_ID"(+))       filter("O"."CUSTOMER_ID"(+)>0)

The query looks for customers which satisfy various conditions. An outer join returns NULL for the inner table columns along with the outer (preserved) table rows when it does not find any corresponding rows in the inner table. This operation finds all the customers rows that do not have any orders rows.

In this case, the outer join condition is the following:

customers.customer_id = orders.customer_id(+)

The components of this condition represent the following:

• The outer table is customers.

• The inner table is orders.

• The join preserves the customers rows, including those rows without a corresponding row in orders.

You could use a NOT EXISTS subquery to return the rows. However, because you are querying all the rows in the table, the hash join performs better (unless the NOT EXISTS subquery is not nested).

In Example 11-10, the outer join is to a multitable view. The optimizer cannot drive into the view like in a normal join or push the predicates, so it builds the entire row set of the view.

Example 11-10 Outer Join to a Multitable View

SELECT c.cust_last_name, sum(revenue)  FROM customers c, v_orders o WHERE c.credit_limit > 2000   AND o.customer_id(+) = c.customer_id GROUP BY c.cust_last_name;----------------------------------------------------------------------------| Id  | Operation              |  Name        | Rows  | Bytes | Cost (%CPU)|----------------------------------------------------------------------------|   0 | SELECT STATEMENT       |              |   144 |  4608 |    16  (32)||   1 |  HASH GROUP BY         |              |   144 |  4608 |    16  (32)||*  2 |   HASH JOIN OUTER      |              |   663 | 21216 |    15  (27)||*  3 |    TABLE ACCESS FULL   | CUSTOMERS    |   195 |  2925 |     6  (17)||   4 |    VIEW                | V_ORDERS     |   665 | 11305 |            ||   5 |     HASH GROUP BY      |              |   665 | 15960 |     9  (34)||*  6 |      HASH JOIN         |              |   665 | 15960 |     8  (25)||*  7 |       TABLE ACCESS FULL| ORDERS       |   105 |   840 |     4  (25)||   8 |       TABLE ACCESS FULL| ORDER_ITEMS  |   665 | 10640 |     4  (25)|----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("O"."CUSTOMER_ID"(+)="C"."CUSTOMER_ID")   3 - filter("C"."CREDIT_LIMIT">2000)   6 - access("O"."ORDER_ID"="L"."ORDER_ID")   7 - filter("O"."CUSTOMER_ID">0)

The view definition is as follows:

CREATE OR REPLACE view v_orders ASSELECT l.product_id, SUM(l.quantity*unit_price) revenue,        o.order_id, o.customer_id  FROM orders o, order_items l WHERE o.order_id = l.order_id GROUP BY l.product_id, o.order_id, o.customer_id;

 Sort Merge Outer Joins

When an outer join cannot drive from the outer (preserved) table to the inner (optional) table, it cannot use a hash join or nested loop joins. Then it uses the sort merge outer join for performing the join operation.

The optimizer uses sort merge for an outer join:

• If a nested loop join is inefficient. A nested loop join can be inefficient because of data volumes.

• The optimizer finds it is cheaper to use a sort merge over a hash join because of sorts required by other operations.

  Full Outer Joins

  

A full outer join acts like a combination of the left and right outer joins. In addition to the inner join, rows from both tables that have not been returned in the result of the inner join are preserved and extended with nulls. In other words, full outer joins let you join tables together, yet still show rows that do not have corresponding rows in the joined tables.

The query in Example 11-11 retrieves all departments and all employees in each department, but also includes:

• Any employees without departments

• Any departments without employees

Example 11-11 Full Outer Join

SELECT d.department_id, e.employee_id  FROM employees e  FULL OUTER JOIN departments d    ON e.department_id = d.department_id ORDER BY d.department_id;

The statement produces the following output:

DEPARTMENT_ID EMPLOYEE_ID------------- -----------           10         200           20         201           20         202           30         114           30         115           30         116...          270          280                      178                      207125 rows selected.

Starting with Oracle Database 11g, Oracle Database automatically uses a native execution method based on a hash join for executing full outer joins whenever possible. When the database uses the new method to execute a full outer join, the execution plan for the query contains HASH JOIN FULL OUTER. Example 11-12shows the execution plan for the query in Example 11-11.

Example 11-12 Execution Plan for a Full Outer Join

----------------------------------------------------------------------------------------| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time      |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT        |            |   122 |  4758 |     6  (34)| 00:0 0:01 ||   1 |  SORT ORDER BY          |            |   122 |  4758 |     6  (34)| 00:0 0:01 ||   2 |   VIEW                  | VW_FOJ_0   |   122 |  4758 |     5  (20)| 00:0 0:01 ||*  3 |    HASH JOIN FULL OUTER |            |   122 |  1342 |     5  (20)| 00:0 0:01 ||   4 |     INDEX FAST FULL SCAN| DEPT_ID_PK |    27 |   108 |     2   (0)| 00:0 0:01 ||   5 |     TABLE ACCESS FULL   | EMPLOYEES  |   107 |   749 |     2   (0)| 00:0 0:01 |---------------------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------   3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Notice that HASH JOIN FULL OUTER is included in the plan. Therefore, the query uses the hash full outer join execution method. Typically, when the full outer join condition between two tables is an equi-join, the hash full outer join execution method is possible, and Oracle Database uses it automatically.

To instruct the optimizer to consider using the hash full outer join execution method, apply the NATIVE_FULL_OUTER_JOIN hint. To instruct the optimizer not to consider using the hash full outer join execution method, apply the NO_NATIVE_FULL_OUTER_JOIN hint. The NO_NATIVE_FULL_OUTER_JOIN hint instructs the optimizer to exclude the native execution method when joining each specified table. Instead, the full outer join is executed as a union of left outer join and an anti-join.

本文来自：http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i49732 

还是看官方文档详细