Menu

Oracle ORA_HASH Function: Buckets, Seed and Examples

Oracle ORA_HASH(expr [, max_bucket [, seed_value]]) returns a numeric hash value that can assign rows to buckets or help select a sample. It returns a NUMBER; for a standard digest such as SHA256, use STANDARD_HASH instead. See the Oracle SQL Language Reference.

Syntax

ORA_HASH(expr [, max_bucket [, seed_value]])

Parameters and return value

  • expr is the value Oracle hashes. It can be any expression except a LONG, LOB, or user-defined object type. Nested-table values are allowed, but their hash does not depend on element order.
  • max_bucket is the inclusive upper bound of the returned bucket number. It can be from 0 to 4294967295; the default is 4294967295.
  • seed_value changes the result for the same expression. It can be from 0 to 4294967295; the default is 0.
  • The function returns a NUMBER. With the default max_bucket, the hash is a 32-bit unsigned number.

If max_bucket is N, Oracle computes the default hash value modulo N + 1. The result is between 0 and N, so max_bucket = 5 creates six possible buckets: 0 through 5.

ORA_HASH does not guarantee a statistically uniform distribution for every bucket count. Oracle notes that the mapping can be biased toward smaller bucket numbers unless max_bucket + 1 is a power of two; the bias may become noticeable for very large bucket counts, especially above 100 million.

Default hash value

With no optional arguments, ORA_HASH returns the default 32-bit hash value:

SELECT ORA_HASH('HELLO') AS hash_value
FROM dual;

HASH_VALUE
----------
671553230

Set a bucket range

Set max_bucket to limit the result range. Since the maximum is inclusive, a value of 100 creates 101 possible buckets (0 to 100):

SELECT
    ORA_HASH('HELLO', 100) AS small_range,
    ORA_HASH('HELLO', 9999999) AS larger_range
FROM dual;

SMALL_RANGE  LARGER_RANGE
-----------  ------------
89           1553230

Change the seed

Use seed_value to get a different bucket assignment for the same expression:

SELECT
    ORA_HASH('Hello', 999999, 1) AS seed_1,
    ORA_HASH('Hello', 999999, 2) AS seed_2
FROM dual;

SEED_1  SEED_2
------  ------
123883  331883

Changing the seed changes the mapping; keep the seed fixed when rows must continue to map to the same buckets.

NULL arguments

If any argument is NULL, ORA_HASH returns NULL:

SET NULL 'NULL';
SELECT
    ORA_HASH(NULL) AS null_expr,
    ORA_HASH('HELLO', NULL) AS null_bucket,
    ORA_HASH('HELLO', 5, NULL) AS null_seed
FROM dual;

NULL_EXPR  NULL_BUCKET  NULL_SEED
---------  -----------  ---------
NULL       NULL         NULL

SET NULL 'NULL' changes how SQL*Plus displays SQL NULL values.

Example: sample rows by bucket

Filter on an ORA_HASH bucket to take one repeatable subset of rows. Here, max_bucket = 99 creates 100 buckets, and the fixed seed keeps the bucket mapping stable:

SELECT customer_id, product_id, amount_sold
FROM sales
WHERE ORA_HASH(customer_id || ':' || product_id, 99, 5) = 0;

For a cryptographic digest with a named algorithm, see STANDARD_HASH.