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: Oracle Text Index

Re: Oracle Text Index

From: Todd Barry <toddbarry_at_earthlink.net>
Date: Fri, 21 Nov 2003 15:11:17 -0800
Message-ID: <5i6trv8cfvfci2c0rvngsv3gmasiclibph@4ax.com>


Peter:

From the docs:



To index, you must create a dummy column to specify in the CREATE INDEX statement. This column’s contents are not made part of the virtual document, unless its name is specified in the columns attribute.
The index is synchronized only when the dummy column is updated. You can create triggers to propagate changes if needed.

So, if your MCD preference specified columns a, b, and c, and the index was built on column a, then you need a trigger that "fakes" an update to column a based on a change in columns b or c. Something like:

create or replace trigger t_trg
before update on t
for each row
begin
  if :new.b <> :old.b or :new.c <> :old.c then     :new.a := :new.a;
  end if;
end;
/  

If column b or c are nullable, be careful of comparisons in the trigger (straight inequality checks will not suffice). You'll need to NVL the values or do the full is null/is not null check.

Hope this helps,
Todd

On Fri, 21 Nov 2003 09:35:13 +0100, Peter Gervais <pgervais_at_mtg.de> wrote:

>Hi there,
>
>We're using an MULTI_COLUMN_DATASTORE TextIndex on one Table T.
>colums A,B and C.
>
>If I insert a new record to table T, I can call sync_index( index1)
>to update the index. But if I update one of these columns, the
>sync_index command has no effect.
>
>I can't use
>'ALTER INDEX Index1 REBUILD' because it takes a lot of time and the
>index is not accessible during the rebulid process.
>
>I've seen on the ctxsys instance over the CTX_PENDING View that
>
>- on an insert operation, an record was created in the DR$PENDING
>table.
>
>- on an update operation, no record was created in the DR$PENDING
>table.
>
>It seems that the sync_index is looking in this table for the
>index update jobs.
>
>Any ideas?
>
>Thanks,
>Peter
Received on Fri Nov 21 2003 - 17:11:17 CST

Original text of this message

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