RE: hash_value and address

We see one such collision about every four years in the application sites who share their data with us. The "hash_value" is definitely not a unique identifier.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas - Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark

-----Original Message-----
Faroult
Sent: Wednesday, September 04, 2002 4:00 PM To: Multiple recipients of list ORACLE-L

Pablo Rodriguez wrote:
>
> I see 2 different addreses for the same hash_value in
> v$sqlarea.
> Why do I see this?
>
> Here's an example:
>
> hash_value address
> ---------- -------
> 3749804 4064082C
> 3749804 4192941C
>
> I wonder if someone can shed some light on this
>
> Txs
>

My understanding is that to quickly find an already parsed statement in memory, Oracle uses a hash function, which, like all hash functions is not surjective (which means that you can have x <>y and f(x)= f(y), in which case you say you have a collision). Of course once the hash value has been computed values are checked, and all strings hashing to the same value are linked - and stored at different addresses. You can also have the case in which statements are strictly identical, but, for session-related reasons, cannot be considered the same cursor (because of synonyms pointing to different objects, of similarly named tables in different schemas, or because optimizer settings have been altered at the session level, typically). In that case it's normal to have the same hash result for the same string - but you nevertheless have a collision of a sort.

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).