Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Text Index
Peter:
From the docs:
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