Avatar of Evan Cutler
Evan Cutler
Flag 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?
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Evan Cutler

8/22/2022 - Mon
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
slightwv (䄆 Netminder)

OH, I think I misread it.

You want to hash all 5 columns....  let me find the code for that...
slightwv (䄆 Netminder)

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;
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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
slightwv (䄆 Netminder)

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)))
Evan Cutler

ASKER
|| Gave me a "FROM KEYWORD NOT FOUND WHERE EXPECTED" error.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

Do you have an extra || at the end of the last column?

Without seeing your SQL, it's sort of hard to diagnose...
Evan Cutler

ASKER
SELECT ORA_HASH(RAW_STR_ADDR_1_TXT || RAW_CITY_TXT||RAW_ST_TXT, 4294967295) from ADDRESS
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Evan Cutler

ASKER
IT WORKS!!!
Thanks
I had an extra ) in the mix...

someone always coming in to ruin the party. lol

thanks much
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy