Oracle ORA_HASH
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Purpose Produces a one-way hash based upon the number of buckets and the seed value specified.
 
Demos
Joining Tables Via Hash ORA_HASH(<value_or_expression>[, <max_bucket>][, <seed_value>]);
conn uwclass/uwclass@pdbdev

CREATE TABLE t1 AS
SELECT owner, table_name, tablespace_name
FROM all_tables;

CREATE TABLE t2 AS
SELECT owner, table_name, tablespace_name
FROM all_indexes;

ALTER TABLE t1
ADD (hashcol NUMBER(38));

ALTER TABLE t2
ADD (hashcol NUMBER(38));

UPDATE t1
SET hashcol = ORA_HASH(owner || table_name ||  tablespace_name);

UPDATE t2
SET hashcol = ORA_HASH(owner || table_name ||  tablespace_name);

CREATE INDEX ix_t1_columns
ON t1 (owner, table_name, tablespace_name);

CREATE INDEX ix_t2_columns
ON t2 (owner, table_name, tablespace_name);

CREATE INDEX ix_t1_hash ON t1 (hashcol);

CREATE INDEX ix_t2_hash ON t2 (hashcol);

set linesize 121

SELECT * FROM t1
WHERE rownum < 101;

SELECT * FROM t2
WHERE rownum < 101;

--=========================================
set serveroutput on

DECLARE
 CURSOR rcur IS
 SELECT t1.table_name, t2.tablespace_name
 FROM t1, t2
 WHERE t1.table_name = t2.table_name
 AND t1.tablespace_name = t2.tablespace_name;

 CURSOR hcur IS
 SELECT t1.table_name, t2.tablespace_name
 FROM t1, t2
 WHERE t1.hashcol = t2.hashcol;

 n PLS_INTEGER;
BEGIN
  n := dbms_utility.get_time;
  FOR i IN 1..1000
  LOOP
    FOR rec IN rcur
    LOOP
      NULL;
    END LOOP;
  END LOOP;

  n := dbms_utility.get_time - n;
  dbms_output.put_line('w/o Hashing: ' || n);

  n := dbms_utility.get_time;
  FOR i IN 1..1000
  LOOP
    FOR rec IN hcur
    LOOP
      NULL;
    END LOOP;
  END LOOP;

  n := dbms_utility.get_time - n;
  dbms_output.put_line('w/ Hashing: ' || n);
END;
/
Sampling Data Via A Hash conn sh/sh@pdbdev

SELECT SUM(amount_sold)
FROM sales
WHERE ORA_HASH(cust_id || prod_id, 99, 5) = 0;

Related Topics
All Functions
Character Functions
Collection Functions
Conversion Functions
Data Mining Functions
Date Functions
DBMS_CRYPTO
DBMS_SQLHASH
Miscellaneous Functions
Numeric Functions
OLAP Functions
Rank
String Functions
XML Functions
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx