Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index on text fields...
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