--source data tables
CREATE TABLE sample1
(id1 NUMBER,
join_val VARCHAR2(20));

CREATE TABLE sample2
(id2 NUMBER,
join_val VARCHAR2(20));

--results of the join
CREATE TABLE res
(id1 NUMBER ,
id2 NUMBER);

--used for functional comparison
CREATE OR REPLACE FUNCTION sample_compare (in1 IN VARCHAR2, in2 IN VARCHAR2)
   RETURN NUMBER
IS
BEGIN
   IF in1 = in2
   THEN
      RETURN 1;
   ELSE
      RETURN 0;
   END IF;
END;

--load some random junk into the tables
DECLARE
   i        NUMBER;
   l_val    VARCHAR2 (20);
   l_len1   NUMBER;
   l_len2   NUMBER;
BEGIN
   FOR i IN 20 .. 20000
   LOOP
      l_len1 := TRUNC (DBMS_RANDOM.VALUE (1, 20));
      l_len2 := TRUNC (DBMS_RANDOM.VALUE (1, 20));
      l_val := DBMS_RANDOM.STRING ('X', 20);

      INSERT INTO sample1
           VALUES (i,
                   SUBSTR (l_val, 1, l_len1));

      INSERT INTO sample2
           VALUES (i,
                   SUBSTR (l_val, 1, l_len2));
   END LOOP;
END;

--get stats for the two tables, replace with your
--default schema
BEGIN
--replace schema name
   DBMS_STATS.gather_table_stats ('NAGEL', 'SAMPLE1');
   DBMS_STATS.gather_table_stats ('NAGEL', 'SAMPLE2');
END;

--normal equality join accomplished via hashing
INSERT INTO res
   SELECT id1,
          id2
     FROM sample1 t1, sample2 t2
    WHERE t1.join_val = t2.join_val;

--function based join accomplished with nested loops at a great
--impact on performance
INSERT INTO res
   SELECT id1,
          id2
     FROM sample1 t1, sample2 t2
    WHERE sample_compare (t1.join_val, t2.join_val) = 1;

--normal join
INSERT STATEMENT  ALL_ROWSCost: 29  Bytes: 642,180  Cardinality: 21,406  		
	3 HASH JOIN  Cost: 29  Bytes: 642,180  Cardinality: 21,406  	
		1 TABLE ACCESS FULL TABLE NAGEL.SAMPLE1 Cost: 14  Bytes: 299,715  Cardinality: 19,981  
		2 TABLE ACCESS FULL TABLE NAGEL.SAMPLE2 Cost: 14  Bytes: 299,715  Cardinality: 19,981   

--functional join
INSERT STATEMENT  ALL_ROWSCost: 258,929  Bytes: 119,772,120  Cardinality: 3,992,404  		
	3 NESTED LOOPS  Cost: 258,929  Bytes: 119,772,120  Cardinality: 3,992,404  	
		1 TABLE ACCESS FULL TABLE NAGEL.SAMPLE1 Cost: 14  Bytes: 299,715  Cardinality: 19,981  
		2 TABLE ACCESS FULL TABLE NAGEL.SAMPLE2 Cost: 13  Bytes: 3,000  Cardinality: 200  

NSERT INTO res
   SELECT id1,
          id2
     FROM sample1 t1, sample2 t2
    WHERE sample_compare (t1.join_val) = sample_compare(t2.join_val);

AND fuzzy_match_func(a.val, b.val) = 'MATCH'