Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index on text fields...

Re: Index on text fields...

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/11
Message-ID: <8i0t2p$u07$1@nnrp1.deja.com>#1/1

In article <8i0ct4$jn9$1_at_nnrp1.deja.com>,   Jonas Malmsten <jonas_at_malmsten.net> wrote:
> 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.
>

check out

dbms_utility.get_hash_value

documentation:
http://technet.oracle.com/doc/server.815/a68001/dbms_uti.htm#1002139

If you need to do this on longs -- we'll be limited to 32k (biggest a plsql variable can be). You'll have to do it in an AFTER, not a FOR EACH ROW trigger. See
http://osi.oracle.com/~tkyte/Mutate/index.html for how to avoid the mutating table you would get if you tried to do this for a LONG in a row level trigger (longs are not available in the :new.field variable in the trigger, we'll have to read the long out in an AFTER trigger, compute the hash...)

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US