As mentioned in the earlier tips, outer join can be done by 3 different techniques. But, from Oracle 10g onwards, Oracle has started to provide the fourth technique to handle the outer join.
· Nested loop outer join
· Hash outer join
· Hash outer right join (from Oracle 10g onwards)
· Merge outer join
Hash outer right join has been provided by Oracle from 10g version onwards. This technique has been introduced in order to overcome the design flaw of hash outer join.
As per the definition of hash outer right join, the smallest of the two tables (where-in outer join is used) will be used to build the hash table, and the other table will be used as the probe table unlike hash outer join, wherein the size of the tables doesn’t play any role and the parent & child table must be used as hash & probe tables respectively.
Hash outer right join will be preferred over hash outer join only if a child table(rows don’t need to be preserved) is outer-joined with a parent table(all the rows must be preserved) and the latter is bigger than the former. In this case, hash outer right join will perform more efficiently than hash outer join and that’s the reason oracle will opt for the former instead of the latter.
If a child table is outer-joined with a parent table and the latter is smaller than the former, oracle will just go ahead with hash outer join as discussed in the previous tip.
To perform this hash outer right join, Oracle follows these steps (assume, a small child table has to be outer-joined with a big parent table):
1. Oracle chooses the small child table as the hash table (otherwise called as driving table). Oracle build the hash table in RAM after applying the hash function on the joining column(s) of the driving table.
2. Oracle chooses the big parent table as the probe table (otherwise called as driven table or probing table). It traverse through all the records of this probe table, applies the same hash function on the joining column(s) [column(s) used to join these two tables] and will hit the corresponding entry in the hash table.
3. Oracle returns the value of all the columns from both the tables if a record from the driving table is already present with the same hash key else the value of all the columns from big parent table will alone be returned & NULL for small child table’s columns mentioned in the query.
As you aware, in hash outer join, deciding which table is going to be the hash table is immaterial and the parent table is always selected as the hash table irrespective of its size. This shows the design flaw in hash outer join as the parent table would be used as the hash table, if it is big, all the records from the big parent table can’t be processed in a single pass, allocated memory space in RAM might not be big enough to hold all the required records from the big parent table, so it has to do multiple passes and takes a lot time. This design flaw has been overcome in hash outer right join. If that small child table was selected as the hash table, none of these issues won’t need to be addressed and that’s what hash outer right join exactly does.
The logical activity diagram for this methodology will be like this,
Read all the required data from the small child table and build the hash table in RAM after applying hash function
Loop (for all the required records in the big parent table)
· Apply the hash function for each record to get the address [hashed value] in RAM
· Hit RAM in the corresponding address
· If (a record from the small child table is already present with the same hash key)
return the columns from both the tables mentioned in the query.
else
return the columns from the big parent table along with NULL values for the small child probing table’s columns mentioned in the query.
End if
End loop
LITTLE-KNOWN FACTS TO BE REMEMBERED:
· /*+ USE_HASH(<<driving table>>) */ is the hint that can be used to impose this hash outer right join.
· This methodology is opted by Oracle only if a small child table is outer join with a big parent table.
· In RAM, hash outer right join will be carried out only in the allocated memory space which is nothing but HASH_AREA_SIZE component of PGA. If this allocated space is not sufficient, it uses TEMP tablespace.
· Only the driving table will be inserted as the hash table in RAM and the driven table will never be written into RAM. Hash function will be applied in the driven table only to locate the corresponding memory entry in the hash table to look for the matching entry from the driving table there.
· This is very successful when the small child table is outer-joined with the big parent table. In this case, it outplays hash outer join.
· If the driving table cannot be hashed in RAM in single pass (i.e., at one shot or one go), then a portion of the hash-table spills to disk(actually, TEMP tablespace will be used here to hold that spilled dataset from the driving table). When the hash table is probed by the driven table, the rows with join keys that match those parts of the in-memory hash table are joined immediately; the rest [of the driven table] are also written into TEMP tablespace and joined in the subsequent passes. The bigger driving table is, the smaller the proportion of the hash table that can fit in RAM, the remaining data will be spilled into TEMP tablespace and have to go through the subsequent passes till it processes all the records spilled into TEMP tablespace from both these driving and driven table. This slows the Hash Outer Right Join down considerably and also makes the join non-scalable in this scenario.
ADVANTAGE:
· If the small child table(driving table) is small enough to fit in RAM, outer join “(+)” operator is used in the query & the parent table is very big, then the hash outer right join will outplay the hash outer join.
· Still nested loop outer join will be the fastest way to retrieve the first matching record [since hash outer right join takes some time to build the hash table in RAM for the small child table as it applies the hash function] but hash outer right join will be the fastest way to get all the matching records quickly when compared to the nested loop outer join. In general, hash function (in hash outer right join) will perform better and more efficiently.
DISADVANTAGE:
· When this is opted for joining 2 big tables, TEMP tablespace will be used extensively as the spilled dataset from both driving and driven tables will be written into TEMP tablespace
· The above scenario makes hash outer right join as more non-scalable as it has to go through subsequent passes to join these spilled dataset (available in TEMP tablespace) from both these tables
HOW TO VERIFY:
How to verify whether Oracle follows hash outer right join or not while executing the sql query. If a query follows this, then you will find similar execution plan like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | | | 1 | HASH JOIN (RIGHT OUTER) | | | |
| 2 | TABLE ACCESS (FULL) | <<small child table’s name>> | | | 3 | TABLE ACCESS (FULL) | <<big parent table’s name>> | | |
In the explain plan, whenever it chooses this methodology, it displays the keyword “HASH JOIN (RIGHT OUTER)” in the operation column. Whichever the table name that is getting displayed immediately after this word is nothing but the driving table (otherwise called as the hash table) and that’s why you will find the small child table’s name in that position followed by the driven table’s name (otherwise called as the probe table) where you will find the big parent table’s name.
EXAMPLE:
In order to understand how the design flaw of hash outer join has been overcome by hash outer right join, we will first discuss the example with the hash outer join then we will discuss with the hash outer right join.
Create 2 tables.
One table is store the information about all the employees and inserts 9 records.
Another table is store the information of the bonus received by the employees and inserts 3 records.
The tables will look like this,
DATA TABLE:
EMP:
ROWID | Empid | Empname | Salary |
AAAAA1 | 3821 | CHARLES | 1000 |
AAAAA2 | 9822 | FERGUSON | 2000 |
AAAAA3 | 7343 | JOHN | 3000 |
AAAAA4 | 1784 | ERIN | 4000 |
AAAAA5 | 5645 | ROONEY | 5000 |
AAAAA6 | 8716 | TOMMY | 6000 |
AAAAA7 | 4737 | MIKE | 7000 |
AAAAA8 | 2388 | NADAL | 8000 |
AAAAA9 | 6119 | ROY | 9000 |
BONUS:
ROWID | Empid | Bonus |
AAAAB1 | 9822 | 600 |
AAAAB2 | 2388 | 850 |
AAAAB3 | 5645 | 400 |
Fire this query against these tables where the requirement is to display the employee’s name, salary & bonus (if they have received) of all the employees available in EMP table,
Select a.empname, a.salary, b.bonus
from emp a, bonus b
where a.empid = b.empid(+);
Assume, Oracle can hold 3 records at a time in the allocated memory space(defined by HASH_AREA_SIZE global parameter) in RAM.
For easy understanding, pls assume MOD function to the base 10 will be used as hash function here. In real world, the hash function would be more complex and hard to decode it.
Case-I: (for hash outer join)
======================
We will discuss what would happen if hash outer join was imposed.
As per this query, emp is the parent table and bonus is the child table since the latter[bonus.empid(+)] is outer-joined with the former. So, oracle will consider emp as the driving able and bonus as the driven table. That’s the reason cost is not the deciding factor and join condition is the deciding factor in hash outer join.
EMPTY HASH TABLE:
=================
Hash Key Data
As per the design of hash outer join, the following steps will be followed while executing this query,
1. Since BONUS is outer-joined with EMP, EMP is considered as the driving table and BONUS is considered as the driven table.
2. Since the memory can hold only 3 records at a time, only the first 3 records will be processed in the first pass and the remaining 6 records will temporarily be kept at TEMP tablespace.
3. For the first record in EMP, hash function is applied and it returns 1 as the hashed value (i.e.,hash(3821)èmod(3821/10)è1).
4. For the next record in EMP, hash function is applied and it returns 2 as the hashed value (i.e.,hash(9822)èmod(9822/10)è2).
5. For the next record in EMP, hash function is applied and it returns 3 as the hashed value (i.e.,hash(7343)èmod(7343/10)è3). So, the hash table will look like this at the end of this step,
Hash Key Data
 |
empid empname salary
1 | 3821 | CHARLES | 1000 |
2 | 9822 | FERGUSON | 2000 |
3 | 7343 | JOHN | 3000 |
6. For the first record in BONUS, hash function is applied and it returns 2 as the hashed value (i.e.,hash(9822)èmod(9822/10)è2)
7. So, it hits 2nd record of hash table
8. Since a record from EMP is already available in 2nd record of hash table, this record will be flagged as “MATCHED” and the data will be returned in the output
9. For the second record in BONUS, hash function is applied and it returns 8 as the hashed value (i.e.,hash(2388)èmod(2388/10)è8)
10. Since, this hashed value (8) is out of range for the currently constructed hash table, it will temporarily be kept in TEMP tablespace
11. For the third & last record in BONUS, hash function is applied and it returns 5 as the hashed value (i.e.,hash(5645)èmod(5645/10)è5)
12. Since, this hashed value (5) is out of range for the currently constructed hash table, it will temporarily be kept in TEMP tablespace
Hash Key Data
 | |||
| |||
empid empname salary
1 | 3821 | CHARLES |  1000 | |
2 | 9822 | FERGUSON |  2000 | MATCHED |
3 | 7343 | JOHN | 3000 | |
13. All the records from hash table is being processed once more those are not flagged as “MATCHED”, all those records (empids:3821,7343) from the hash table will be returned along with NULL for the small child table’s columns mentioned [bonus column in this case] in the query.
14. Since the memory can hold only 3 records at a time, then the next 3 EMP records from TEMP tablespace will be processed in this second pass.
15. For the next record of EMP, hash function is applied and it returns 4 as the hashed value (i.e.,hash(1784)èmod(1784/10)è4).
16. For the next record of EMP, hash function is applied and it returns 5 as the hashed value (i.e.,hash(5645)èmod(5645/10)è5).
17. For the next record of EMP, hash function is applied and it returns 6 as the hashed value (i.e.,hash(8716)èmod(8716/10)è6). So, the hash table will look like this at the end of this step,
Hash Key Data
|
empid empname salary
4 | 1784 | ERIN | 4000 |
5 | 5645 | ROONEY | 5000 |
6 | 8716 | TOMMY | 6000 |
18. Since only 2 records from BONUS table is available in TEMP tablespace, only these 2 records will be used for probing the hash table.
19. For the first record of BONUS in TEMP tablespace, hash function is applied and it returns 8 as the hashed value (i.e.,hash(2388)èmod(2388/10)è8).
20. Since, this hashed value (8) is out of range for the currently constructed hash table, it will temporarily be kept at TEMP tablespace again.
21. For the second & last record of BONUS in TEMP tablespace, hash function is applied and it returns 5 as the hashed value (i.e.,hash(5645)èmod(5645/10)è5).
22. Since a record from EMP is already available in 2nd record of hash table, this record will be flagged as “MATCHED” and the data will be returned in the output.
Hash Key Data
 | |||||
| |||||
 | |||||
empid empname salary 4 | 1784 | ERIN | 4000 | |
5 | 5645 | ROONEY | 5000 | MATCHED |
6 | 8716 | TOMMY | 6000 | |
23. All the records from hash table is being processed once more those are not flagged as “MATCHED”, all those records (empids:1784,8716) from the hash table will be returned along with NULL for the small child table’s columns mentioned [bonus column in this case] in the query.
24. Since the memory can hold only 3 records at a time, then the next (& last) 3 EMP records from TEMP tablespace will be processed in this third pass.
25. For the next record of EMP, hash function is applied and it returns 7 as the hashed value (i.e.,hash(4737)èmod(4737/10)è7).
26. For the next record of EMP, hash function is applied and it returns 8 as the hashed value (i.e.,hash(2388)èmod(2388/10)è8).
27. For the next record of EMP, hash function is applied and it returns 9 as the hashed value (i.e.,hash(6119)èmod(6119/10)è9). So, the hash table will look like this at the end of this step,
Hash Key Data
|
empid empname salary
7 | 4737 | MIKE | 7000 |
8 | 2388 | NADAL | 8000 |
9 | 6119 | ROY | 9000 |
28. Since only one record from BONUS table is available in TEMP tablespace, only this record will be used for probing the hash table.
29. For this record of BONUS in TEMP tablespace, hash function is applied and it returns 8 as the hashed value (i.e.,hash(2388)èmod(2388/10)è8).
30. Since a record from EMP is already available in 2nd record of hash table, this record will be flagged as “MATCHED” and the data will be returned in the output.
Hash Key Data
 | |||
| |||
empid empname salary
7 | 4737 | MIKE | 7000 | |
8 | 2388 | NADAL | 8000 | MATCHED |
9 | 6119 | ROY | 9000 | |
31. All the records from hash table is being processed once more those are not flagged as “MATCHED”, all those records (empids:4737,6119) from the hash table will be returned along with NULL for the small child table’s columns mentioned [bonus column in this case] in the query.
32. Since no more records from both EMP & BONUS tables are available in TEMP tablespace, it exits.
So, the output will look like this,
Empname | Salary | Bonus |
FERGUSON | 2000 | 600 |
CHARLES | 1000 | |
JOHN | 3000 | |
ROONEY | 5000 | 400 |
ERIN | 4000 | |
TOMMY | 6000 | |
NADAL | 8000 | 850 |
MIKE | 7000 | |
ROY | 9000 |
Now, explain table will look like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 4 | SELECT STATEMENT | | 9 | | 3 | HASH JOIN (OUTER) | | 9 | |
| 1 | TABLE ACCESS (FULL) | EMP | 18 | | 2 | TABLE ACCESS (FULL) | BONUS | 6 | |
From the explain plan, we can say that both the tables are joined by hash outer join methodology, EMP is considered as the driving table [hash table] and BONUS is considered as the driven table [probe table].
If you closely look at the above executed steps, TEMP tablespace has been used extensively to store the data of both EMP & BONUS temporarily. 3 passes were required to process all the records from both the tables. This (performance issue) happened since the big parent table has been used to build the hash table and the small child table has been used to probe the hash table (as per hash outer join). If you closely look at the values mentioned in the last column “Rows” of the explain plan, all the values have been shoot up since TEMP tablespace has been used extensively and multiple passes went through. This performance issue will be overcome by hash outer right join. We will discuss this next.
Case-II: (for hash outer right join)
==========================
We will discuss what would happen if hash outer right join was imposed.
As per this query, EMP is the parent table and BONUS is the child table since the latter[bonus.empid(+)] is outer-joined with the former.
As per hash outer right join, EMP is the probing table and BONUS is the hash table since the latter is smaller than the former. That’s the reason cost is the deciding factor and not the join condition in hash outer right join.
EMPTY HASH TABLE:
=================
Hash Key Data
As per the design of hash outer right join, the following steps will be followed while executing this query,
1. Since BONUS is outer-joined with EMP & the latter is bigger than the former, BONUS is considered as the driving table and EMP is considered as the driven table.
2. Since the memory can hold 3 records at a time, only 3 records are available in BONUS table, everything will get processed in a single pass.
3. For the first record in BONUS, hash function is applied and it returns 2 as the hashed value (i.e.,hash(9822)èmod(9822/10)è2).
4. For the next record in BONUS, hash function is applied and it returns 8 as the hashed value (i.e.,hash(2388)èmod(2388/10)è8).
5. For the next (& last) record in BONUS, hash function is applied and it returns 5 as the hashed value (i.e.,hash(5645)èmod(5645/10)è5). So, the hash table will look like this at the end of this step,
Hash Key Data
 |
empid bonus
2 | 9822 | 600 |
5 | 2388 | 850 |
8 | 5645 | 400 |
6. EMP table is considered as the probing table as that is the parent table and bigger than BONUS table.
7. For the first record in EMP, hash function is applied and it returns 1 as the hashed value (i.e.,hash(3821)èmod(3821/10)è1)
8. Since, this hashed value (1) is not available in the currently constructed hash table, only the value of EMP table’s columns will be returned and NULL for “salary” column of BONUS table.
9. For the second record in EMP, hash function is applied and it returns 2 as the hashed value (i.e.,hash(9822)èmod(9822/10)è2)
10. Since a record from BONUS table is already available with the same hash key value as the 1st record in hash table, the value for both the tables’ columns will be returned in the output
11. For the third record in EMP, hash function is applied and it returns 3 as the hashed value (i.e.,hash(7343)èmod(7343/10)è3)
12. Since, this hashed value (3) is not available in the currently constructed hash table, only the value of EMP table’s columns will be returned and NULL for “salary” column of BONUS table.
13. For the fourth record in EMP, hash function is applied and it returns 4 as the hashed value (i.e.,hash(1784)èmod(1784/10)è4)
14. Since, this hashed value (4) is not available in the currently constructed hash table, only the value of EMP table’s columns will be returned and NULL for “salary” column of BONUS table.
15. For the fifth record in EMP, hash function is applied and it returns 5 as the hashed value (i.e.,hash(5645)èmod(5645/10)è5)
16. Since a record from BONUS table is already available with the same hash key value as the 2nd record in hash table, the value for both the tables’ columns will be returned in the output
17. For the sixth record in EMP, hash function is applied and it returns 6 as the hashed value (i.e.,hash(8716)èmod(8716/10)è6)
18. Since, this hashed value (6) is not available in the currently constructed hash table, only the value of EMP table’s columns will be returned and NULL for “salary” column of BONUS table.
19. For the seventh record in EMP, hash function is applied and it returns 7 as the hashed value (i.e.,hash(4737)èmod(4737/10)è7)
20. Since, this hashed value (7) is not available in the currently constructed hash table, only the value of EMP table’s columns will be returned and NULL for “salary” column of BONUS table.
21. For the eighth record in EMP, hash function is applied and it returns 8 as the hashed value (i.e.,hash(2388)èmod(2388/10)è8)
22. Since a record from BONUS table is already available with the same hash key value as the 3rd record in hash table, the value for both the tables’ columns will be returned in the output
23. For the ninth (& last) record in EMP, hash function is applied and it returns 9 as the hashed value (i.e.,hash(6119)èmod(6119/10)è9)
24. Since, this hashed value (9) is not available in the currently constructed hash table, only the value of EMP table’s columns will be returned and NULL for “salary” column of BONUS table.
Hash Key Data
 | |||||||
 | |||||||
| |||||||
 | |||||||
empid bonus 2 | 9822 |    600 |
5 | 2388 |   850 |
8 | 5645 |  400 |
25. Since no more records from both EMP & BONUS tables to be processed so it exits.
So, the output will look like this,
Empname | Salary | Bonus |
CHARLES | 1000 | |
FERGUSON | 2000 | 600 |
JOHN | 3000 | |
ERIN | 4000 | |
ROONEY | 5000 | 400 |
TOMMY | 6000 | |
MIKE | 7000 | |
NADAL | 8000 | 850 |
ROY | 9000 |
Now, explain table will look like this,
-------------------------------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | |
-------------------------------------------------------------------------------------------------------- |
| 4 | SELECT STATEMENT | | 9 | | 3 | HASH JOIN (RIGHT OUTER) | | 9 | |
| 1 | TABLE ACCESS (FULL) | BONUS | 3 | | 2 | TABLE ACCESS (FULL) | EMP | 9 | |
From the explain plan, we can say that both the tables are joined by hash outer right join methodology, BONUS is considered as the driving table [hash table] and EMP is considered as the driven table [probe table].
If you closely look at the above executed steps, in a single pass, all the records from both the tables have been processed. Unlike hash outer join, since BONUS table is considered as the hash table, all its records have been inserted into the memory in a single pass. This is how the performance has been improved as all the records have been processed in a single pass & TEMP tablespace has never been used. This is how it outplays hash outer join. If you closely look at the values mentioned in the last column “Rows” of the explain plan, all the values have been lesser than of hash outer join. So, the design flaw of hash outer join has finally been taken care by hash outer right join.
Comments
Post a Comment