Oracle Database - Hash joins

Card Puncher Data Processing

About

Hash Join

When the Optimizer Uses Hash Joins

The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:

  • A large amount of data needs to be joined.
  • A large fraction of a small table needs to be joined.

Example

The table orders is used to build the hash table, and order_items is the larger table, which is scanned later.

SELECT o.customer_id, l.unit_price * l.quantity
  FROM orders o ,order_items l
 WHERE l.order_id = o.order_id;

--------------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |   665 | 13300 |     8  (25)|
|*  1 |  HASH JOIN           |              |   665 | 13300 |     8  (25)|
|   2 |   TABLE ACCESS FULL  | ORDERS       |   105 |   840 |     4  (25)|
|   3 |   TABLE ACCESS FULL  | ORDER_ITEMS  |   665 |  7980 |     4  (25)|
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("L"."ORDER_ID"="O"."ORDER_ID")

Hash Join Hints

Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables together.

Documentation / Reference





Discover More
Consistent Hashing
Cryptography - Hash

A hash function is an encryption crypto algorithm that takes as data as input (possibly large and of variable-sized) and produces a short fixed-length integer value (generally printed as an hexadecimal...
Card Puncher Data Processing
Oracle Database - How to detect read from the temporary tablespace ?

How to detect read from the temporary tablespace ? Step followed : disable automatic PGA management by the server set the hash area size. and play with the sort area size In this example, the...
Card Puncher Data Processing
Oracle Database - Join (Operation|Method|Mechanism|Algorithm)

Joins are data operation / method / algorithm in order to match data from more than one table. They all require sorting and then matching aspects of a dataset. There are only three join mechanisms used...
Parallel Linearly Scalable Operation
Oracle Database - Parallel Operations

The fundamental difference and advantage of Oracle's capabilities, however, is that parallel data access and therefore the necessary data redistribution are not constrained by any given hardware architecture...
Card Puncher Data Processing
Oracle Database - Parallel execution with Oracle Partitioning (parallel partition-wise join)

There are specific optimizations between SQL parallel execution and Oracle Partitioning that you should bear in mind when you plan to use these functionalities together. For example, two large partitioned...
Oracle Database Star Transformation
Oracle Database - Star Transformation

This article talk the application of the star query in Oracle which involve a star schema. Star transformation executes the query in two phases: retrieves the necessary rows from the fact table (row...
Oracle Database Direct Path Read Temp With Parallel Slave
Oracle Database - direct path read temp and direct path read wait event

“direct path read temp” and “direct path read” event are wait events. When a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event....
Relational Algebra Between Sql And Query Plan
Relation Operator - Nested Loop Join

Nested loop joins are join operator. They are useful when small subsets of data are being joined and if the join condition is an efficient way of accessing the second table. NESTED LOOPS is a row operation....
Query Plan For The Same Query Response Time
Sql Engine - (Physical|Execution) Plan

A physical plan (or execution plan) is an ordered tree of physical relational operator that represent the execution a query A logical plan is also a ordered tree of relational operator but without the...



Share this page:
Follow us:
Task Runner