Re: hash value - function

On Aug 14, 12:19 pm, ciapecki <ciape..._at_gmail.com> wrote:
> On 14 Aug., 18:21, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > Comments embedded.
> > On Aug 14, 10:54 am, ciapecki <ciape..._at_gmail.com> wrote:
>
> > > Hi,
>
> > > Is there any function (10g) that would return a kind of hash value,
>
> > Which release of 10g?
>
> > > I was thinking of getting one value for the whole table.
>
> > I'm wondering why you'd think to do that.
>
> > > My idea was to concatenate all the columns (5 columns there not very
> > > big around 10chars each) get a numerical hash value.
> > > Then group by primary_key, and get a sum or avg, or whatever.
>
> > What possible benefit could that provide?
>
> > > If the hash value would be big enough I could use it for comparing if
> > > two tables are the same of the content or not.
>
> > How? Methinks this be folly of a grand order.
>
> > > the question is, if there is such a function, I could not find it that
> > > takes varchar2 and returns a value.
>
> > DBMS_CRYPTO.HASH_MD4
> > DBMS_CRYPTO.HASH_MD5
> > DBMS_CRYPTO.HASH_SH1
>
> > are available, but they won't hash an entire table.
>
> > > thanks
> > > chris
>
> > I would be seriously re-thinking this idea; what business case does it
> > support? I have a strong feeling this is a path which is rapidly
> > going nowhere.
>
> > David Fitzjarrell
>
> The idea is the same as zipping and unzipping files, the software
> creates a kind of hash value for the file, and after unpacking it
> checks if the new counted hash value is the same that was originally,
> and then it means we got the same file.
>
> I wanted to use this procedure to check periodically if a table
> changed.
> I would save the original hash value, and run periodically checks on
> the table if the hash value is different,
> if it is the case it means the content of the table changed.
>
> chris- Hide quoted text -
>
> - Show quoted text -

Why go about this convoluted act? The USER_TAB_MODIFICATIONS view provides such information; a simple process of monitoring that view and reporting any changes should suffice. You have still not answered the question of what business case this satisfies; my guess is none. To 'hash' an entire table simply to see if one column in one row has changed is exerting far more effort than the task is worth, in my opinion.

I would be investigating the tools already in place which Oracle has provided, rather than scheming to devise new and devilish mechanisms which are prone to failure and impact the database and user community in a negative manner for absolutely no gain whatsoever.

David Fitzjarrell Received on Tue Aug 14 2007 - 12:59:05 CDT