ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Produces a one-way hash based upon the number of
buckets and the seed value specified.
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;
