Oracle Learning----A special way to connect

1. Cartesian product Merge Join Cartesion

Sql> select Ename,dname from Emp,dept;

56 rows have been selected.

Execution plan
----------------------------------------------------------
Plan Hash value:2034389985

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 896 | 10 (0) | 00:00:01 |
| 1 | MERGE JOIN cartesian| | 56 | 896 | 10 (0) | 00:00:01 |
| 2 | TABLE ACCESS Full | DEPT | 4 | 36 | 3 (0) | 00:00:01 |
| 3 | BUFFER SORT | | 14 | 98 | 7 (0) | 00:00:01 |
| 4 | TABLE ACCESS Full | EMP | 14 | 98 | 2 (0) | 00:00:01 |
-----------------------------------------------------------------------------

The result of this Cartesian product is that we set it up because there's no connection condition.

In the real system.

1. Optimizer is easy to cause the Cartesian product when it is wrong

2. No associated conditions

Disable the Cartesian product of the hint/*+ opt_param (' _optimizer_mjc_enabled ', ' false ') */

2. External connection

1.a LEFT join on B if there is a nested circular relationship between A and B, the driver table can only be a

2. If a and B are hash joins

Sql> Select Ename,dname from the EMP left JOIN dept on Emp.deptno=dept.deptno;

14 rows have been selected.

Execution plan
----------------------------------------------------------
Plan Hash value:3387915970

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 588 | 7 (15) | 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 588 | 7 (15) | 00:00:01 |
| 2 | TABLE ACCESS full| EMP | 14 | 280 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS full| DEPT | 4 | 88 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------

Leading no use, can not change who is the driver table

Sql> Select/*+use_hash (emp,dept) leading (dept) */Ename,dname from the EMP left JOIN dept on Emp.deptno=dept.deptno;

14 rows have been selected.

Execution plan
----------------------------------------------------------
Plan Hash value:3387915970

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 588 | 7 (15) | 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 588 | 7 (15) | 00:00:01 |
| 2 | TABLE ACCESS full| EMP | 14 | 280 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS full| DEPT | 4 | 88 | 3 (0) | 00:00:01 |
---------------------------------------------------------------------------

To change can only use Swap_join_inputs () This is dedicated to change the hash join of the driver table

Sql> Select/*+use_hash (emp,dept) swap_join_inputs (Dept) */Ename,dname from EMP Left JOIN dept on Emp.deptno=dept.dept No

14 rows have been selected.

Execution plan
----------------------------------------------------------
Plan Hash value:4261033907

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 588 | 7 (15) | 00:00:01 |
|* 1 | HASH JOIN right OUTER| | 14 | 588 | 7 (15) | 00:00:01 |
| 2 | TABLE ACCESS Full | DEPT | 4 | 88 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS Full | EMP | 14 | 280 | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------

3. Scalar Quantum Query

A query in a 1.select clause is a scalar subquery, try not to use a scalar subquery except in a paging statement

2. Must be used when you want to create an index on the connection column of the driver table, which is indexed on the filtered column of the table of the scalar subquery.

3. Change to an external connection when you can rewrite it.

4. There is a custom function in SQL, just as with the scalar query

Oracle Learning----A special way to connect