Shulou(Shulou.com)06/01 Report--

Swap_join_inputs is a hint for hash join, which means that the optimizer can exchange the order of the driven table and the driven table of the original hash join, that is, the drive table of the original hash join is changed into the driven table while the hash join is still going.

Note that the target table specified in swap_join_inputs hint should be the driven table in the original hash join, otherwise oracle will ignore the hint.

/ * + swap_join_inputs (the driven table of the original hash join) * /

Examples of its use are as follows:

Select / * + leading (dept) use_hash (emp) swap_join_intputs (emp) * / * from emp,dept whereemp.deptno=dept.deptno

Test case:

SCOTT@ORA12C > create table T1 as select * from dba_objects where rownum create table T2 as select * from dba_objects where rownum create table T3 as select * from dba_objects where rownum exec dbms_stats.gather_table_stats (ownname = > 'SCOTT',tabname = >' T1 parallel as select = > 100 for all columns size cascade = > true,method_opt = > 'for all columns size 1 writing parallelism notarized invalidate = > false) PL/SQL procedure successfully completed.SCOTT@ORA12C > exec dbms_stats.gather_table_stats (ownname = > 'SCOTT',tabname = >' T2), PL/SQL procedure successfully completed.SCOTT@ORA12C > exec dbms_stats.gather_table_stats (ownname = > 'SCOTT',tabname = > T3), > 100cascade = > true,method_opt = >' for all columns size 1), PL/SQL procedure successfully completed.

The records of the three tables are as follows:

SCOTT@ORA12C > select count (*) from T1; COUNT (*)-11 row selected.SCOTT@ORA12C > select count (*) from T2; COUNT (*)-111row selected.SCOTT@ORA12C > select count (*) from T3; COUNT (*)-211row selected.

Now let's let tables T2 and T3 do a hash join. Since there are more records in the T3 table than in the T2 table, T3 is designated as the driven table for the hash join:

Select / * + ordered use_hash (T3) * / t2.objectpapernameMagi t3.objectpapertype 2 from t2jint3 where t2.object_id=t3.object_id Execution Plan---Plan hash value: 1730954469 | | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT | STATEMENT | | 11 | 220,6 (0) | 00:00:01 | * 1 | HASH JOIN | | 11 | 220,6 (0) | 00:00:01 | 2 | TABLE ACCESS FULL | T2 | 11 | 110 | 3 | 00:00:01 | 3 | TABLE ACCESS FULL | T3 | 21 | 210 | 3 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-1-access ("T2". "OBJECT_ID" = "T3". "OBJECT_ID")

As you can see, the above SQL execution plan now follows a hash connection and is driven to represent table T3.

If we want to change the hash connected driven table from T3 to T2, we can add swap_join_inputs hint to the above sql:

Select / * + ordered use_hash (T3) swap_join_inputs (T3) * / T2. ObjectpapernameJournal t3.objectwriting type 2 from T2 where t2.object_id=t3.object_id Execution Plan---Plan hash value: 1723280936 Murray- | | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT | STATEMENT | | 11 | 220,6 (0) | 00:00:01 | * 1 | HASH JOIN | | 11 | 220,6 (0) | 00:00:01 | 2 | TABLE ACCESS FULL | T3 | 21 | 210 | 3 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | T2 | 11 | 110 | 3 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-1-access ("T2". "OBJECT_ID" = "T3". "OBJECT_ID")

The same can be achieved with leading (T3) use_hash (T2):

Select / * + leading (T3) use_hash (T2) * / T2. ObjectpapernameJournal t3.objectwriting type 2 from T2 where t2.object_id=t3.object_id Execution Plan---Plan hash value: 1723280936 Murray- | | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 0 | SELECT | STATEMENT | | 11 | 220,6 (0) | 00:00:01 | * 1 | HASH JOIN | | 11 | 220,6 (0) | 00:00:01 | 2 | TABLE ACCESS FULL | T3 | 21 | 210 | 3 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | T2 | 11 | 110 | 3 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-1-access ("T2". "OBJECT_ID" = "T3". "OBJECT_ID")

This shows that when two tables are associated, you can use other hint instead of swap_join_inputs to achieve the same purpose:

What about multiple table associations:

Select / * + ordered use_hash (T3) * / t1.ownerdiary t2.objectpapernameretype2 from T2 where t2.object_id=t3.object_id and t1.object_type=t3.object_type Execution Plan---Plan hash value: 98820498 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | 4 | 120 | 9 (0) | 00:00:01 | * 1 | HASH JOIN | 4 | 120 | 9 (0) | 00:00:01 | * 2 | HASH JOIN | 11 | 220 | 6 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | T2 | 11 | 110 | 3 (0) | 00:00:01 | 4 | TABLE ACCESS FULL | T3 | 21 | 210 | 3 | 3 | (0) | 00:00:01 | 5 | TABLE ACCESS FULL | T1 | 1 | 10 | 3 (0) | 00:00:01 |-Predicate Information (identified by operation id ):-1-access ("T1". "OBJECT_TYPE" = "T3". "OBJECT_TYPE") 2-access ("T2". "OBJECT_ID" = "T3". "OBJECT_ID"

As you can see, the execution plan of the above sql is to make a hash join by table T2 and table T3, and then make a hash connection between them and table T1.

The number of records in table T1 is 1, the number of records in table T2 is 11, and the number of records in table T3 is 21, so when table T2 and T3 make a hash join, table T3 with a large number of records should be driven. This is because we use ordered hint and use_hash HINT to specify table T3 as the driven table when tables T2 and T3 are connected in the above sql, so oracle chose tables T2 and T3 to do hash join. There is no problem that table T3 is selected as the driven table of the hash join, but now the problem is that the number of records of table T1 is only 1, so when tables T2 and T3 do the result of hashing connection and then do hash connection with table T1, table T1 should be the driven table, not the driven table as the second hash join as shown in the above execution plan.

Use the following HINT:

Select / * + ordered use_hash (T3) * / t1.ownerdiary t2.objectpapernamejournal t3.objectcallitype 2 from T1 where t2.object_id=t3.object_id and t1.object_type=t3.object_type Execution Plan---Plan hash value: 38266800 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 4 | 120 | 9 (0) | 00:00:01 | | * 1 | HASH JOIN | 4 | 120 | 9 (0) | 00:00:01 | | 2 | MERGE JOIN CARTESIAN | | 11 | 220 | 6 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | T1 | 1 | 10 | 3 (0) | 00:00:01 | 4 | BUFFER SORT | | 11 | 110 | 3 (0) | 00:00:01 | 5 | TABLE ACCESS FULL | T2 | 11 | 110 | 3 (0) | 00:00:01 | 6 | TABLE ACCESS FULL | T3 | 21 | 21 | 3 (0) | 00:00:01 |-- -Predicate Information (identified by operation id):-1-access ("T2". "OBJECT_ID" = "T3". "OBJECT_ID" AND "T1". "OBJECT_TYPE" = "T3". "OBJECT_TYPE") select / * + leading (T1) use_hash (T3) * / t1.owner T2.object_name,t3.object_type 2 from t1,t2,t3 where t2.object_id=t3.object_id and t1.object_type=t3.object_type Execution Plan---Plan hash value: 2308542799 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | 7 | 210 | 9 (0) | 00:00:01 | * 1 | HASH JOIN | 7 | 210 | 9 (0) | 00:00:01 | * 2 | HASH JOIN | 7 | 140 | 6 (0) | 00:00:01 | 3 | TABLE ACCESS FULL | T1 | 1 | 10 | 3 (0) | 00:00:01 | 4 | TABLE ACCESS FULL | T3 | 21 | 210 | 3 | 3 | (0) | 00:00:01 | 5 | TABLE ACCESS FULL | T2 | 11 | 110 | 3 (0) | 00:00:01 |-Predicate Information (identified by operation Id):-1-access ("T2". "OBJECT_ID" = "T3". "OBJECT_ID") 2-access ("T1". "OBJECT_TYPE" = "T3". "OBJECT_TYPE")

Add the following hint to solve the problem:

SELECT / * + ordered use_hash (T3) swap_join_inputs (T1) * / t1.owner, t2.object_name, t3.object_type FROM T2, T3, T1 WHERE t2.object_id = t3.object_id 5 AND t1.object_type = t3.object_type Execution Plan---Plan hash value: 3071514789 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-- | 0 | SELECT STATEMENT | 4 | 120 | 9 (0) | 00:00:01 | * 1 | HASH JOIN | 4 | 120 | 9 (0) | 00:00:01 | 2 | TABLE ACCESS FULL | T1 | 1 | 10 | 3 (0) | 00:00:01 | * 3 | HASH JOIN | 11 | 220 | 6 (0) | 00:00:01 | 4 | TABLE ACCESS FULL | T2 | 11 | 110 | 3 | (0) | 00:00:01 | 5 | TABLE ACCESS FULL | T3 | 21 | 21 | 3 (0) | 00:00:01 |-Predicate Information (identified by operation id ):-1-access ("T1". "OBJECT_TYPE" = "T3". "OBJECT_TYPE") 3-access ("T2". "OBJECT_ID" = "T3". "OBJECT_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.