| Demo |
Joining Tables
Via A Hash |
ORA_HASH(<value>, <max_buckets>,
<seed_value>) |
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
SELECT SUM(amount_sold) FROM sales
WHERE ORA_HASH(cust_id || prod_id, 99, 5) = 0;
|
|
