EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle hash join

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Oracle hash join

Oracle hash join

Introduction to Oracle hash join

In a hash join a hash table is created which is a technique used for joining two or more tables. Whenever the memory of the computer is made to set up at that time the best choice of the oracle’s optimizer is to use the hash join. Whenever we are joining two or more tables, the oracles optimizer selects the smallest table out of them and generates a hash table on that join using the join key for those tables. After that, the optimizer chooses the other remaining table which is usually a bigger one in that join for scanning, and then the hash table and that table are together probed to find the appropriate match.

Conditions for hash join to occur

The Oracle optimizer chooses hash join in a very efficient manner only when the parameter PGA_AGGREGATE_TARGET has a very large value which is sufficient enough to carry out the join. In case if we use a MEMORY_TARGET parameter, then the value of MEMORY_TARGET is added to the PGA_AGGREGATE_TARGET and together considered for hash join. But still, it is suggested to set both of the parameter values to the minimum necessary. Specification of SGA_TARGET does not include the value of parameter PGA_AGGREGATE_TARGET in it. Hence, even if the SGA_TARGET is set, you must also set the PGA_AGGREGATE_TARGET.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Whenever the nested loops join becomes inefficient because of the absence of a useful index then hash joins prove to be very efficient. There might be a situation when hash join can behave even faster than that of sort-merge join. Because in the case of the hash join probing different values of the hash table proves to be much faster than that of b- tree index traversing. Hash joins are useful only when there are equijoins. The working of hash join can become slow if the sort memory is not sufficient because if this happens then the hash joins to use the I/O services and memory resources of the temporary space for the tables. We can use the hash joins in oracle only when we use the cost-based optimization. This is the most frequent scenario that we have if our application is running on the Oracle 11g. The building of a hash table on one of the tables is the only affecting factor that contributes to the cost of hash join.

Example

Consider that there are two tables named the customers and the stores. Both the tables are joined based on the join key named store id. The contents of the customers and stores table are as shown in the below image –

Customers Table –

Oracle hash join output 1

Stores Table –

Oracle hash join output 2

Suppose that the SQL query which is fired is as shown below on the table customers and stores on the join key store_id.

SELECT cust.name, store.store_id
FROM Stores AS stores
JOIN Customers AS cust
WHERE cust.store_id = store.store_id;

When the hash join is performed on the above-shown customer’s table and stores the table as the set of input then the oracle optimizer internally generates a hash table on the smaller table values. In this example, the hash table will be generated based on the join key which is store_id for the table customers in memory. Further, the optimizer takes every record of the customer’s table and probes for the matching value by comparing it to the hash table. All the records for the customer table are checked for the value in the store table.

For, each record in the store table all the records of the customer table are scanned and the loop continues till all the store records are completed for match and all the records are segregated. In the case of stores, the number of the loops that will be necessary to perform the hash join to sort will be for three times.

The first loop will find all the records of the customer’s table who have the store id as 1. The records retrieved after the first loop when the records are matched for the store is 1 are as shown below –

Oracle hash join output 3

The second loop will segregate all the records of the customer’s table who will have the store id as 2. The records retrieved after the first loop when the records are matched for the store is 2 are as shown below –

output 3

Finally, the last scan will be made to find all the matching records with store id having value 3. The records retrieved after the first loop when the records are matched for the store is 3 are as shown below –

output 5

The fourth loop will not return any value because there were none of the records in the customer table with store id as 4.

When the hash table does not fit in the PGA area then the oracle optimizer uses the temporary space to accommodate the small portions of the hash table. These temporary space portions are also called partitions. Other than the hash table, some parts of the large table are also stored in partitions. The process includes the following steps followed by the oracle database’s algorithm.

It performs the complete scan on the smaller table which contains less number of the records compared to both the tables of join and stores the hash buckets in PGA as well as on disk. When the PGA area fills up then the biggest part of the hash table is chosen and stored in the temporary space of the disk. In this way, when the PGA area is not enough the data is stored in both the memory as well as the disk partition.

Conclusion

In the hash join technique whenever a join is performed in oracle in cost-based optimization manner a hash table is generated based on the join key on the smaller table. Further, all the records of the bigger table are scanned one by one to probe the matching values. This process continues for all the join key hash values. We have to be careful that the PGA variables are set to proper values to accommodate all the data of the hash tables.

Recommended Articles

This is a guide to Oracle hash join. Here we discuss the Conditions for hash join to occur along with the examples and outputs. You may also have a look at the following articles to learn more –

  1. Oracle INSTR()
  2. Oracle GRANT
  3. Oracle Synonyms
  4. Oracle SYSDATE()

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Oracle Course Training Learn More