cool!
September 27, 2001 - 1pm Central time zone
Reviewer: Mikito Harakiri
BTW, I was trying a similar thing
CREATE INDEX my_idx ON my_table (SUBSTR(clob_column,1,100));
and it didn't work
explicit hash function in the equality predicate
September 27, 2001 - 1pm Central time zone
Reviewer: Mikito Harakiri
On pessimistic note, the ideal solution would avoid explicitly mentioning hash function in the
equality predicate -- it is physical access implementation detail after all. I guess this is too
much to ask from the optimizer today...
no equality predicate on clobs
September 27, 2001 - 9pm Central time zone
Reviewer: Mikito Harakiri
Hmm... It took me a while to notice to_char function in your predicate.
There seems no equality predicate on clobs to begin with. CLOB manual says:
Non-Supported SQL Functionality for LOBs
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The following SQL functionality is not supported for LOBs because the functions are either
infrequently used or have easy workarounds.
...
Operators: >, <, =, !=
Followup September 28, 2001 - 6am Central time zone:
well, you seem to be getting less and less "happy" with this answer.
The fact is CLOBS are big, huge, not really things that you want to be doing equals on -- you have
to force it in some cases.
I would question why you need to do this -- compare clobs -- it will be slow, the tips here will
help you make it faster, more realistic....
no equality predicate on clobs
September 28, 2001 - 1pm Central time zone
Reviewer: Mikito Harakiri
I'm collecting sql statements from huge ERP application. Most of the statements are reasonably
short, while the longest one is 30K bytes long. I need to be able to identify identical statements,
as I also would like to peek into V$SQL. Therefore, I'm not comparing huge clobs at all.
Even if I would, knowing that todays database limits are somewhere under 1B records, we can think
of hash function that can make this comparison efficient. This hash heed to have a range > 1B*1B in
order to eleminate chances of collision.
On the other hand, you are probably correct, naive literal comparison of clobs woldn't get me too
far. In my example, ignoring differences in the names of bind variables would probably be better
comparison criteria. Looks like for big objects only user-defined equality makes sence.
Another way of computing a checksum for anysize clob
March 17, 2003 - 5pm Central time zone
Reviewer: Yves Azie
create or replace and compile
java source named "GetChecksum"
as
import java.io.*;
import java.lang.*;
import java.security.*;
/**
* This class takes in a clob, and computes
* a unique hash value based on the md5 hash algorythm
*/
public class GetChecksum extends Object
{
public static float get(oracle.sql.CLOB clobToCompute) throws Exception
{
BufferedReader clobReader = new BufferedReader(clobToCompute.getCharacterStream());
String s = null;
StringBuffer sb = new StringBuffer();
while ((s = clobReader.readLine()) != null) {
sb.append(s);
}
clobReader.close();
byte[] clobBytes = sb.toString().getBytes();
MessageDigest md5 = MessageDigest.getInstance("MD5");
byte[] clobResult = md5.digest(clobBytes);
ByteArrayInputStream bytestream1 = new ByteArrayInputStream (clobResult);
DataInputStream bytestream2 = new DataInputStream( bytestream1 );
float clobFloat = bytestream2.readFloat();
return clobFloat;
}
}
/
show err
create or replace function checksum ( p_lob in clob )
return number
DETERMINISTIC
as
language java
name 'GetChecksum.get(oracle.sql.CLOB) return float';
/
show err
You may then use this function in SQL as follows:
select checksum(clobfield) from clobTable;
Comparing CLOBs
June 22, 2005 - 5pm Central time zone
Reviewer: Anirudh from NJ, USA
Hi Tom,
We have this requirement of comparing two CLOBS or rather do a stuff which is similar to "Track
Changes" in Microsoft Word. We would like to have a program which would compare two CLOBs and
return the differences (line no., word no. or whatever).
I figure that there is no simple and easy way to go about it, can you please suggest an approach
which would help us in doing this job.
Again, our requirement is just to compare two CLOBS and point out the differences to the users.
Please feel free to ignore this if you feel this question is irrelevant to the context of the
parent topic.
Thanks as always.
Anirudh
Followup June 23, 2005 - 1pm Central time zone:
there is no simple way to do this, you'd have to implement it or find an implementation of it...
MD5 checksum on a clob
August 11, 2005 - 9am Central time zone
Reviewer: Carsten Rüthel from BY Germany
Hi,
we have modified Yves Azie's code to get "real" MD5 in a 32 digit hex code.
create or replace and compile
java source named "GetChecksum"
as
import java.io.*;
import java.lang.*;
import java.security.*;
/**
* This class takes in a clob, and computes
* a unique hash value based on the md5 hash algorythm
*/
public class GetChecksum extends Object
{
public static String byteToHex(byte b){
int i = b & 0xFF;
String res = Integer.toHexString(i);
if ( res.length() == 0 ) return ( "00" );
if ( res.length() == 1 ) return ( "0"+res );
return ( res );
}
public static String byteArr2Hex( byte[] b ) {
String res = new String();
for ( int ix=0; ix < b.length; ++ix ) {
res += byteToHex(b[ix]);
}
return ( res );
}
public static String get(oracle.sql.CLOB clobToCompute) throws Exception
{
BufferedReader clobReader = new BufferedReader(clobToCompute.getCharacterStream());
String s = null;
StringBuffer sb = new StringBuffer();
while ((s = clobReader.readLine()) != null) {
sb.append(s);
}
clobReader.close();
byte[] clobBytes = sb.toString().getBytes();
MessageDigest md5 = MessageDigest.getInstance("MD5");
byte[] clobResult = md5.digest(clobBytes);
return ( byteArr2Hex ( clobResult ));
}
}
|