Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the method of NL optimization only if there are OR connection conditions in the database?" in the operation of the actual case, many people will encounter such a dilemma, then let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

test data

Drop table T1 purge;drop table T2 purge;create table T1 (id int,name varchar2 (10), age int); insert into T1 values (1); insert into T1 values (2); insert into T1 values (3); values (4); insert into T1 values (5); insert into T1 values (6); create table T2 (id int,name varchar2 (10)) Insert into T2 values, insert into T2 values,

External joins with OR association conditions can only go to NL. If the result set of the driver table is large, it will produce a large number of associations, which will cause performance problems and need to be optimized.

There are several situations when two tables are externally joined:

1. When making an external join, nl is used, and the main table is fixed as the driver table, and the driver table cannot be adjusted through hint.

2. When making an external connection, using hash, you can adjust the driven table and the driven table through hint.

Do the following experiments for external connections:

1. When the execution plan is nl,t1 is the driven table (master table), T2 is the driven table, T2 is the driven table, and T1 is the driven table.

2. When the execution plan is nl,t1 is the driven table (master table), T2 is the driven table, adjust the execution plan to hash.

3. When the execution plan is hash,t1 is the driven table (master table), T2 is the driven table, T2 is the driven table, and T1 is the driven table.

4. When the execution plan is hash,t1 is the driven table (master table), T2 is the driven table, adjust the execution plan to nl.

5. When the external connection has an OR association condition (T1.ID = T2.ID OR T1.AGE = T2.ID), rewrite it equivalently.

The execution plan is the case of nl

The following SQL is available:

SELECT T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 T1_ID T1_NAME T1_AGE T2_ID T2 name-1 a 1 a 2 b 2 2b 3 c 5 3 c 4 d 1 5 e 36 f 6 implementation plan: Plan hash value: 3645848104- -| Id | Operation | Name | Starts | E-Rows | A- Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |- -- | 0 | SELECT STATEMENT | | 1 | | 6 | 00 00.01 | 11 | | 1 | SORT ORDER BY | | 1 | 6 | 6 | 00:00 |: 00.01 | 11 | 2048 | 2048 | 2048 (0) | | 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 | 00 00.01 | 11 | | 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 00Plus 00.01 | 7 | | | 4 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 3 | 00VERV 00.01 | 4 | * 5 | INDEX RANGE SCAN | IDX_ID_T2_01 | 6 | 1 | 3 | 00VERV 00.01 | 3 | |- -Predicate Information (identified by operation id):-- 5-access ("T1". "ID" = "T2". "ID")

As you can see from the execution plan, the nl is gone, and T1 is the driver table.

1. When the execution plan is nl,t1 is the driven table (master table), T2 is the driven table, T2 is the driven table, and T1 is the driven table.

In the inner join, the adjustment of the driven table and the driven table can be realized, but the order of the driven table cannot be adjusted in the outer join.

SELECT / * + leading (T2 T1) use_nl (T1) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 Plan hash value: 109855138- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 00001 | 11 | | | 1 | SORT ORDER BY | | 1 | 6 | 6 | 00TABLE ACCESS FULL 00.01 | 11 | 2048 | 2048 | 2048 (0) | | 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 | 00TABLE ACCESS FULL | 11 | 3 | T1 | 1 | 6 | 00 IDX_ID_T2 00.01 | 7 | 4 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 3 | 00 IDX_ID_T2 00.01 | 4 | * 5 | IDX_ID_T2 | 6 | 1 | | 3 | 000.00 | 00.01 | 3 |-| -Predicate Information (identified by operation id):-5-access ("T1". "ID" = "T2". "ID")

As you can see from the execution plan, the driver table or T1 does not change the execution order, so the driver table and the driver table cannot be performed in the external join where the execution plan is nl.

Adjustment of the driven table.

The reason why it cannot be adjusted:

When making an external join, T1 is the main table, and the left outer join is T2, so you need to return all the data of T1. The nested loop needs to pass a value, and after the master table passes the value to the slave table

If it is found that there is no association from the table, it can be directly displayed as NULL

However, if the value is passed from the table to the master table, the data without association cannot be passed to the master table, and it is impossible to pass NULL to the master table, so the association between the two tables is an external join.

The nested loop-driven table can only be fixed as the master table.

2. When the execution plan is nl,t1 is the driven table (master table), T2 is the driven table, adjust the execution plan to hash.

Find a way to adjust it to hash

Use hint:use_hash ()

Driver table: T1

Driven table: T2

SELECT / * + leading (T2 T1) use_hash (T1) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 Plan hash value: 109855138- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 00001 | 11 | | | 1 | SORT ORDER BY | | 1 | 6 | 6 | 00TABLE ACCESS FULL 00.01 | 11 | 2048 | 2048 | 2048 (0) | | 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 | 00TABLE ACCESS FULL | 11 | 3 | T1 | 1 | 6 | 00 IDX_ID_T2 00.01 | 7 | 4 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 3 | 00 IDX_ID_T2 00.01 | 4 | * 5 | IDX_ID_T2 | 6 | 1 | | 3 | 000.00 | 00.01 | 3 |-| -Predicate Information (identified by operation id):-5-access ("T1". "ID" = "T2". "ID")

At this time, the hint is not in effect, and the original nl connection has been taken.

Try a different hint

SWAP_JOIN_INPUTS: describe who does the built-in table (driver table) NO_SWAP_JOIN_INPUTS: explain who does the probe table (driven table) SELECT / * + leading (T2 T1) use_hash (T1) no_swap_join_inputs (T1) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 Plan hash value: 109855138- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 00001 | 11 | | | 1 | SORT ORDER BY | | 1 | 6 | 6 | 00TABLE ACCESS FULL 00.01 | 11 | 2048 | 2048 | 2048 (0) | | 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 | 00TABLE ACCESS FULL | 11 | 3 | T1 | 1 | 6 | 00 IDX_ID_T2 00.01 | 7 | 4 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 3 | 00 IDX_ID_T2 00.01 | 4 | * 5 | IDX_ID_T2 | 6 | 1 | | 3 | 000.00 | 00.01 | 3 |-| -Predicate Information (identified by operation id):-5-access ("T1". "ID" = "T2". "ID")

At this time, the hint is not in effect, and the original nl connection has been taken.

The reason is the same as that of nl, the principle of the driven table and the driven table is the same, only the connection mode of the table can be changed, but the access order of the table can not be changed.

3. When the execution plan is hash,t1 is the driven table (master table), T2 is the driven table, T2 is the driven table, and T1 is the driven table.

Find a way to adjust the access order of the table

Use hint:use_hash ()

Driver table: T2

Driven table: T1

SELECT / * + leading (T2 T1) use_hash (T1) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 Plan hash value: 2391546071-| Id | | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |- -- | 0 | SELECT STATEMENT | | 1 | 6 | 00 00.01 | 14 | 1 | SORT ORDER BY | 1 | 6 | 6 | 00Rank 00.01 | 14 | 2048 | 2048 | 2048 (0) | | * 2 | HASH JOIN OUTER | | 1 | 6 | 00.01 | 14 | 1753K | 1753K | 1753K | 920K (0) | 3 | TABLE ACCESS FULL | T1 | 6 | 7 | 4 | TABLE ACCESS FULL | T2 | 1 | 3 | 00lv 00.01 | 7 |- -Predicate Information (identified by operation id):-- 2-access ("T1". "ID" = "T2". "ID")

As you can see from the execution plan, the driver table or T1 does not change the order of execution.

Need to add a hintSWAP_JOIN_INPUTS: explain who does the built-in table (driver table) NO_SWAP_JOIN_INPUTS: explain who does the probe table (driven table) SELECT / * + leading (T2 T1) use_hash (T1) swap_join_inputs (T2) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 Plan hash value: 2146067096- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |- -- | 0 | SELECT STATEMENT | | 1 | | 6 | 00 00.01 | 14 | | 1 | SORT ORDER BY | | 1 | 6 | 6 | 00Plus 00hand00.01 | | 14 | 2048 | 2048 | 2048 (0) | | * 2 | HASH JOIN RIGHT OUTER | | 1 | 6 | 6 | 00TABLE ACCESS FULL 00.01 | 14 | 2061K | 2061K | 872k (0) | 3 | TABLE ACCESS FULL | T2 | 1 | 3 | 00TABLE ACCESS FULL | T1 | 1 | 7 | 6 | 6 | 000.00 | 00.01 | 7 |- -- Predicate Information (identified by operation id):-- 2-access ("T1". "ID" = "T2". "ID")

From the execution plan, you can see that the driven table has become T2, the driven table has become T1, and you can see the operation of id=2.

From the original HASH JOIN OUTER to HASH JOIN RIGHT OUTER, this part is equivalent.

Equivalent to T1 left outer connection T2 is rewritten to T2 right outer connection T1.

SELECT / * + leading (T2 T1) use_hash (T1) no_swap_join_inputs (T1) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 Plan hash value: 2391546071-| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |- -- | 0 | SELECT STATEMENT | | 1 | 6 | 00 SELECT STATEMENT 00.01 | 14 | 1 | SORT ORDER BY | | 1 | 6 | 6 | 00 00.01 | 14 | 2048 | 2048 | 2048 (0) ) | | * 2 | HASH JOIN OUTER | | 1 | 6 | 6 | 00RV 00.01 | 14 | 1753K | 1753K | 886K (0) | | 3 | TABLE ACCESS FULL | T1 | 6 | 6 | 00VRV 00.01 | 7 | 4 | TABLE ACCESS FULL | T2 | 1 | 3 | 00RU 00.01 | | | 7 |-| -Predicate Information (identified by operation id):-- 2-access ("T1". "ID" = "T2". "ID")

Whether the hint will take effect at this time, or the original execution plan.

4. When the execution plan is hash,t1 is the driven table (master table), T2 is the driven table, adjust the execution plan to nl.

Adjust hash to nl

Driver table: T1

Driven table: T2

Id of T2 creates index create index idx_id_t2 on T2 (id); SELECT T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 Plan hash value: 109855138- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 00001 | 11 | | | 1 | SORT ORDER BY | | 1 | 6 | 6 | 00TABLE ACCESS FULL 00.01 | 11 | 2048 | 2048 | 2048 (0) | | 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 | 00TABLE ACCESS FULL | 11 | 3 | T1 | 1 | 6 | 00 IDX_ID_T2 00.01 | 7 | 4 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 3 | 00 IDX_ID_T2 00.01 | 4 | * 5 | IDX_ID_T2 | 6 | 1 | | 3 | 000.00 | 00.01 | 3 |-| -Predicate Information (identified by operation id):-5-access ("T1". "ID" = "T2". "ID")

The execution plan has changed from hash to nl, and T1 is the driven table and T2 is the driven table

Adjust hash to nl

Driver table: T2

Driven table: T1

The id of T1 creates the index create index idx_id_t1 on T1 (id); SELECT / * + leading (T2 T1) use_nl (T1) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON T1.ID = T2.IDORDER BY 1 Plan hash value: 109855138- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 00001 | 11 | | | 1 | SORT ORDER BY | | 1 | 6 | 6 | 00TABLE ACCESS FULL 00.01 | 11 | 2048 | 2048 | 2048 (0) | | 2 | NESTED LOOPS OUTER | | 1 | 6 | 6 | 00TABLE ACCESS FULL | 11 | 3 | T1 | 1 | 6 | 00 IDX_ID_T2 00.01 | 7 | 4 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 3 | 00 IDX_ID_T2 00.01 | 4 | * 5 | IDX_ID_T2 | 6 | 1 | | 3 | 000.00 | 00.01 | 3 |-| -Predicate Information (identified by operation id):-5-access ("T1". "ID" = "T2". "ID")

As you can see from the execution plan, the driver table or T1 does not change the order of execution.

The reason is the same as that of nl, the principle of the driven table and the driven table is the same, only the connection mode of the table can be changed, but the access order of the table can not be changed.

5. When the outer connection has OR association condition, rewrite it equivalently (2)

SELECT T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON (T1.ID = T2.ID OR T1.AGE = T2.ID) ORDER BY 1 T1_ID T1_NAME T1_AGE T2_ID T2 name-1 a 1 a 2 b 2 2b 3 c 5 3 c 4 d 1 1 a 5 e 3 3 c 6 f 66 rows selected.Plan hash value: 3004654521- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used- Mem |-| 0 | SELECT STATEMENT | | 1 | 6 | 00 NESTED LOOPS OUTER 00.01 | 49 | | 1 | SORT ORDER BY | 1 | 6 | 6 | 00 NESTED LOOPS OUTER 00.01 | 49 | 2048 | 2048 | 2048 (0) | 2 | NESTED LOOPS OUTER | 1 | 6 | 0 | 49 | | 3 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 | 00VRV 00.01 | 7 | 4 | VIEW | | 6 | 1 | 5 | 00RO | 00.01 | 42 | * 5 | TABLE ACCESS FULL | T2 | 6 | 1 | 5 | 000.00 TABLE ACCESS FULL | 42 | -Predicate Information (identified by operation id):-5-filter (("T1". "ID" = "T2". "ID" OR "T1". "AGE" = "T2". "ID")

T1 as the main table and T2 for external connection, need to return all the data of T1 and T2 records that meet the conditions, as well as the result null of T2 that does not meet the conditions.

When OR is used, it means that T1 and T2 records can be returned as long as one of these conditions is met.

Suppose T1 and T2 connect from the first row of records: when T1 takes the id and age values of the first row of records to T2 table, and matches the first row records of T2 table, there are three cases: 1. If the id value of T1 is found to be equal to the id value of T2 table, but the age value of T1 is not equal to the id value of T2 table, then return the record of T1 and the record of T2, the record of the first row. 2. If the age value of T1 is found to be equal to the id value of T2 table, but the id value of T1 is not equal to the id value of T2 table, then the record of T1 and T2 and the record of the first row are also returned. 3. If the id value of T1 and the id value of age and T2 table are found to be equal, then the record of T1 and T2 and the record of the first row are also returned. The result of these three cases is that T2 returns null if either a record is returned or not satisfied. When the first row of records is matched, then the second row of records of T1 and the second row of T2 should be matched. The matching method and situation are still the same as the above method. It is not until all the records of T1 are matched that the records that meet the conditions and the null of T2 that do not meet the conditions are finally obtained. So in this case, you need a line of data to match, so the optimizer chose to use nl, which requires nested loops of matching data.

There must be something wrong with the implementation plan at this time:

1. The driven table is a full table scan, the join column has no index, T1 sends a piece of data, T2 needs a full table scan.

2. Generally speaking, when using nl, the small table comes first and the big table comes last, but in the external connection, if you go nl, or determine the main table, then he must be the driver table.

The main table here can be either a table or a filtered result set, so when the result set of the main table is very large, the driver table needs to be driven many times.

Do a lot of join operations, consuming a lot of resources.

Several situations:

T1 is a small table, T2 is a large table, but T2 column has no index, all are full table scans.

T1 is a small table, T2 is a small table, but T2 column has no index, all are full table scans

T1 is a large table, T2 is a large table, but the T2 column has no index and is a full table scan.

T1 is a large table, T2 is a small table, but T2 column has no index, all are full table scans.

The above operations are all problematic, using nl, but the driven tables are all table scans.

In other cases, the join column of the T2 table has an index

T1 is a small table, T2 is a large table, but T2 column has an index

T1 is a small table, T2 is a small table, but T2 column has an index

T1 is a large table, T2 is a large table, but T2 column has an index

T1 is a large table, T2 is a small table, but T2 column has an index

The above operations have improved performance compared to full table scans, but there is also a large number of join.

When the id column of T2 has an index

Create index idx_id_t2 on T2 (id); SELECT T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON (T1.ID = T2.ID OR T1.AGE = T2.ID) ORDER BY 1 Plan hash value: 2234 182087- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 000.00 SELECT STATEMENT 00.01 | 24 | | | 1 | SORT ORDER BY | | 1 | 12 | 6 | 00 00.01 | 24 | 2048 | 2048 | 2048 (0) | 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 | 00Plus 00001 | 24 | 3 | | TABLE ACCESS FULL | T1 | 1 | 6 | 0 00.01 | 7 | 4 | VIEW | 6 | 2 | 5 | 00 CONCATENATION | 17 | 5 | | | 6 | 5 | 00 IDX_ID_T2 00.01 | 17 | 6 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 4 | 00 IDX_ID_T2 00.01 | 10 | * 7 | IDX_ID_T2 | | 6 | 2 | 4 | 00 IDX_ID_T2 00.01 | 6 | 8 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 1 | 00 IDX_ID_T2 00.01 | 7 | * 9 | IDX_ID_T2 | 6 | 2 | 1 | | | 000.00 / 00.01 | 6 |-| -Predicate Information (identified by operation id):-7-access ("T1". "AGE" = "T2". "ID") 9- Access ("T1". "ID" = "T2". "ID") filter (LNNVL ("T1". "AGE" = "T2". "ID"))

Because the join conditions are all related to the id of T2, when there is an index on the join condition of T2, the index is used, but two index scans are performed, and then the table is returned.

Then use the result set as a view.

T1 gives a record, then scan the view once, which is also problematic.

There are the following problems when using the above operations:

1. The access method is fixed and only nl can be used, regardless of whether the join column of the driven table has an index or not.

2. When the driven table is large and the driven table is very small, the efficiency of using nl is very low, and the driven table needs to access the number of rows of T1 (result set).

Optimization ideas:

1. Adjust the order of driven tables and driven tables

2. Use hash

1. Adjust the order of driven tables and driven tables

SELECT / * + leading (T2 T1) use_nl (T1) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON (T1.ID = T2.ID OR T1.AGE = T2.ID) ORDER BY 1 Plan hash value: 2234 182087- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 000.00 SELECT STATEMENT 00.01 | 24 | | | 1 | SORT ORDER BY | | 1 | 12 | 6 | 00 00.01 | 24 | 2048 | 2048 | 2048 (0) | 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 | 00Plus 00001 | 24 | 3 | | TABLE ACCESS FULL | T1 | 1 | 6 | 0 00.01 | 7 | 4 | VIEW | 6 | 2 | 5 | 00 CONCATENATION | 17 | 5 | | | 6 | 5 | 00 IDX_ID_T2 00.01 | 17 | 6 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 4 | 00 IDX_ID_T2 00.01 | 10 | * 7 | IDX_ID_T2 | | 6 | 2 | 4 | 00 IDX_ID_T2 00.01 | 6 | 8 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 1 | 00 IDX_ID_T2 00.01 | 7 | * 9 | IDX_ID_T2 | 6 | 2 | 1 | | | 000.00 / 00.01 | 6 |-| -Predicate Information (identified by operation id):-7-access ("T1". "AGE" = "T2". "ID") 9- Access ("T1". "ID" = "T2". "ID") filter (LNNVL ("T1". "AGE" = "T2". "ID"))

Reason: the external connection of nl cannot change the driven table.

2. Use hash

SELECT / * + leading (T1 T2) use_hash (T2) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON (T1.ID = T2.ID OR T1.AGE = T2.ID) ORDER BY 1 Plan hash value: 2234 182087- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 000.00 SELECT STATEMENT 00.01 | 24 | | | 1 | SORT ORDER BY | | 1 | 12 | 6 | 00 00.01 | 24 | 2048 | 2048 | 2048 (0) | 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 | 00Plus 00001 | 24 | 3 | | TABLE ACCESS FULL | T1 | 1 | 6 | 0 00.01 | 7 | 4 | VIEW | 6 | 2 | 5 | 00 CONCATENATION | 17 | 5 | | | 6 | 5 | 00 IDX_ID_T2 00.01 | 17 | 6 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 4 | 00 IDX_ID_T2 00.01 | 10 | * 7 | IDX_ID_T2 | | 6 | 2 | 4 | 00 IDX_ID_T2 00.01 | 6 | 8 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 1 | 00 IDX_ID_T2 00.01 | 7 | * 9 | IDX_ID_T2 | 6 | 2 | 1 | | | 000.00 / 00.01 | 6 |-| -Predicate Information (identified by operation id):-7-access ("T1". "AGE" = "T2". "ID") 9- Access ("T1". "ID" = "T2". "ID") filter (LNNVL ("T1". "AGE" = "T2". "ID"))

Hint with hash

SWAP_JOIN_INPUTS: describe who does the built-in table (driver table) NO_SWAP_JOIN_INPUTS: explain who does the probe table (driven table) SELECT / * + leading (T1 T2) use_hash (T2) swap_join_inputs (T1) * / T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON (T1.ID = T2.ID OR T1.AGE = T2.ID) ORDER BY 1 Plan hash value: 2234 182087- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 000.00 SELECT STATEMENT 00.01 | 24 | | | 1 | SORT ORDER BY | | 1 | 12 | 6 | 00 00.01 | 24 | 2048 | 2048 | 2048 (0) | 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 | 00Plus 00001 | 24 | 3 | | TABLE ACCESS FULL | T1 | 1 | 6 | 0 00.01 | 7 | 4 | VIEW | 6 | 2 | 5 | 00 CONCATENATION | 17 | 5 | | | 6 | 5 | 00 IDX_ID_T2 00.01 | 17 | 6 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 4 | 00 IDX_ID_T2 00.01 | 10 | * 7 | IDX_ID_T2 | | 6 | 2 | 4 | 00 IDX_ID_T2 00.01 | 6 | 8 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 1 | 00 IDX_ID_T2 00.01 | 7 | * 9 | IDX_ID_T2 | 6 | 2 | 1 | | | 000.00 / 00.01 | 6 |-| -Predicate Information (identified by operation id):-7-access ("T1". "AGE" = "T2". "ID") 9- Access ("T1". "ID" = "T2". "ID") filter (LNNVL ("T1". "AGE" = "T2". "ID")) SELECT / * + leading (T1 T2) use_hash (T2) no_swap_join_inputs (T2) * / T1.ID T1_ID T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAMEFROM T1LEFT JOIN T2ON (T1.ID = T2.ID OR T1.AGE = T2.ID) ORDER BY 1 Plan hash value: 2234 182087- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-- -| 0 | SELECT STATEMENT | | 1 | | 6 | 000.00 SELECT STATEMENT 00.01 | 24 | | | 1 | SORT ORDER BY | | 1 | 12 | 6 | 00 00.01 | 24 | 2048 | 2048 | 2048 (0) | 2 | NESTED LOOPS OUTER | | 1 | 12 | 6 | 00Plus 00001 | 24 | 3 | | TABLE ACCESS FULL | T1 | 1 | 6 | 0 00.01 | 7 | 4 | VIEW | 6 | 2 | 5 | 00 CONCATENATION | 17 | 5 | | | 6 | 5 | 00 IDX_ID_T2 00.01 | 17 | 6 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 4 | 00 IDX_ID_T2 00.01 | 10 | * 7 | IDX_ID_T2 | | 6 | 2 | 4 | 00 IDX_ID_T2 00.01 | 6 | 8 | TABLE ACCESS BY INDEX ROWID | T2 | 6 | 1 | 1 | 00 IDX_ID_T2 00.01 | 7 | * 9 | IDX_ID_T2 | 6 | 2 | 1 | | | 000.00 / 00.01 | 6 |-| -Predicate Information (identified by operation id):-7-access ("T1". "AGE" = "T2". "ID") 9- Access ("T1". "ID" = "T2". "ID") filter (LNNVL ("T1". "AGE" = "T2". "ID"))

Unable to adjust the execution plan to hash.

The final idea:

An equivalent rewrite is required so that such a query execution plan does not leave nl, or that the driver table can be changed (impossible, as mentioned earlier, the external join of nl cannot change the driver table).

Therefore, only consider equivalent rewriting, used to eliminate the impact of or.

When making an equivalent rewriting, there are two cases:

1. There are no duplicate values in the id field of T2

2. There are duplicate values in the id field of T2

When there is no duplicate value in the id field of T2, rewrite it equivalently (thanks to Mr. Guo):

SELECT * FROM (SELECT T.*, ROW_NUMBER () OVER (PARTITION BY T.T1_RID ORDER BY T.T2_ID) RN FROM (SELECT T1.ID T1_ID, T1.NAME T1_NAME, T1.AGE T1_AGE, T2.ID T2_ID) T2.NAME T2_NAME, T1.ROWID T1_RID FROM T1 LEFT JOIN T2 ON T1.ID = T2.ID UNION ALL SELECT T1.ID T1_ID, T1.NAME T1_NAME T1.AGE T1_AGE, T2.ID T2_ID, T2.NAME T2_NAME, T1.ROWID T1_RID FROM T1 LEFT JOIN T2 ON T1.AGE = T2.ID) T) WHERE RN = 1order by 1 T1_ID T1_NAME T1_AGE T2_ID T2_NAME T1_RID RN -1 a 1 1 an AAAVuJAAEAAAByUAAA 1 2 b 2 2 b AAAVuJAAEAAAByUAAB 1 3 c 5 3 c AAAVuJAAEAAAByUAAC 1 4 d 1 1 a AAAVuJAAEAAAByUAAD 1 5 e 3 3 c AAAVuJAAEAAAByUAAE 16 f 6 AAAVuJAAEAAAByUAAF 16 rows selected.Plan hash value: 3180408145 Mel- -| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used- Mem |- -| 0 | SELECT STATEMENT | | 1 | 6 | 00 VIEW 00.01 | 28 | 1 | SORT ORDER BY | 1 | 12 | 6 | 0 | 28 | 2048 | 2048 | 2048 (0) | * 2 | VIEW | 1 | 12 | 6 | 00.01 | 28 | * 3 | WINDOW SORT PUSHED RANK | | 1 | 12 | 2048 | 2048 (0) | 28 | 2048 | 2048 | 2048 (0) | 4 | VIEW | 1 | 12 | 12 | 00.01 | 28 | | | 5 | UNION-ALL | | 1 | | 12 | 00 TABLE ACCESS 00.01 | 28 | * 6 | HASH JOIN OUTER | | 1 | 6 | 6 | 00 TABLE ACCESS | 14 | 1321K | 1321K | 939K (0) | | 7 | FULL | T1 | 1 | 6 | 00 HASH JOIN OUTER 00.01 | 7 | 8 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 | 00 | * 9 | HASH JOIN OUTER | 1 | 6 | 6 | 00 : 00 00.01 | 14 | 1321K | 1321K | 939K (0) | | 10 | TABLE ACCESS FULL | T1 | 1 | 6 | 6 | 00VRV 00.01 | 7 | 11 | TABLE ACCESS FULL | T2 | 3 | 3 | 00RV 00.01 | 7 |-- -Predicate Information (identified by Operation id):-2-filter ("RN" = 1) 3-filter (ROW_NUMBER () OVER (PARTITION BY "T". "T1_RID" ORDER BY "T". "T2_ID")

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.