Oracle ORA_HASH
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
   
 
 
Demos
Joining Tables Via Hash ORA_HASH(<value>, <max_buckets>, <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@pdborcl

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

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-2014 Daniel A. Morgan All Rights Reserved