Index on text fields...

From: Jonas Malmsten <jonas_at_malmsten.net>
Date: 2000/06/11
Message-ID: <8i0ct4$jn9$1@nnrp1.deja.com>#1/1

I need to do a duplicate check on any new text inserted in a table similar to

create table x (
  id constraint pk_x primary key,
  text varchar2(4000)
  );

Table x contain close to 1000000 records and the field text contains an arbitrary text (could also be declared as a long field).

How do I accompish this? I was thinking of adding a column (hash integer) and have a trigger calculate a hash value for each new text inserted or updated. Then I could index this column and dupcheck this before I dupcheck the actual text. The problem with this is that I can't find any binary operators in Oracle, such as binary xor and binary rotate, for calulating the hash value. Any suggestions on how to solve any of these problems would be appreciated. I'm working on a thin client system, therefor I want to solve as much as possible using the database server only. I would also like to avoid any 3:rd party packages etc (anything not provided with Oracle 8 Enterprise edition).

//Jonas

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Jun 11 2000 - 00:00:00 CDT