Link to home
Start Free TrialLog in
Avatar of Evan Cutler
Evan CutlerFlag for United States of America

asked on

ORACLE ORA_HASH to USE as JOIN

Greetings...
I'm being asked to use ORACLE HASHES to perform JOINs...

I have two tables.  each with 5 fields each...
unfortunately I have to join against all 5 fields, and it's slowing down the lookup.

I was told to implement a ORACLE HASH in the second table, and perform the join against the HASH from the first table.

How do I do this?  Am I even asking this question correctly?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I am against hints as a general rule but to try and force the join, that is what you need:

http://docs.oracle.com/cd/B12037_01/server.101/b10752/hintsref.htm#5142
OH, I think I misread it.

You want to hash all 5 columns....  let me find the code for that...
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions124.htm#i1235081

In the examples:
...
  WHERE ORA_HASH(CONCAT(cust_id, prod_id), 99, 5) = 0;
Avatar of Evan Cutler

ASKER

CONCAT limits to two fields...
how do I get all five?

It's for an address....  NUMBER/STREET, CITY, STATE, ZIP, COUNTRY

Thanks
use normal string concatination?

ora_hash(NUMBERSTREET || CITY || STATE || ZIP || COUNTRY)

Depending on the data, you might want to attempt some cleanup:

ora_hash(to_lower(trim(NUMBERSTREET) || trim(CITY) || trim(STATE) || trim(ZIP) || trim(COUNTRY)))
|| Gave me a "FROM KEYWORD NOT FOUND WHERE EXPECTED" error.
Do you have an extra || at the end of the last column?

Without seeing your SQL, it's sort of hard to diagnose...
SELECT ORA_HASH(RAW_STR_ADDR_1_TXT || RAW_CITY_TXT||RAW_ST_TXT, 4294967295) from ADDRESS
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
IT WORKS!!!
Thanks
I had an extra ) in the mix...

someone always coming in to ruin the party. lol

thanks much