8.1.7 Performance on Varchar2(4000) = Varchar2(4000)

[trevgf]

Task, improve performance on a join of two tables with approx 200,000 rows each with varchar2(4000) fields. There is a context index on these columns but it doesn't come into play. I can obviously create an index on these ( after I've got the data on a database of blocksize 16K as opposed to 8K as present ).

BUT what I'm interested in is if I added a column which was a checksum giving a value say between 1 and 64K and had an index put on it, to be evaluated on insert or update of these tables, then the join could first narrow down the number of rows that may be joined before the long varchars get read, by joining on this.

So has anyone tried setting something like this up before and did it work? If it didn't do you think it would ever be worthwhile or was it just your data distribution that made this not viable.

[slimdave]

Sure, you could create a new column, and populate it with the result of DBMS_UTILITY.GET_HASH_VALUE() applied to the string, and join on that.

One word of caution is that the hashing function can change from release to release, so after an upgrade you would do well to recalculate the hash values for all the rows, but apart from that you should be ok.

[DaPi]

Hi Trev,

I'm intrigued to know what the application is that looks for EXACT equality between 4000 char strings?

I've played with "A is pretty much the same as B" in various contexts - but nothing that's relevent. If anyone is interested, latest Scientific American had an article on comparing Chain Letters to determine their pattern of evolution.

(The hash idea looks good to me.)