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: hexathioorthooxalate <ruler_at_REMOVESPAM.clara.co.uk>
Date: Sat, 22 Nov 2003 22:01:00 -0000
Message-Id: <1069538462.44315.0@iris.uk.clara.net>


Todd - clear answer.

The underlying reason is that a single database column is formally indexed with an Oracle Text MULTI_COLUMN_DATASTORE index [eg the ON table_name(column_name) part of the CREATE INDEX STATEMENT], whereas the underlying Oracle text index actually contains information derived from other columns that may not formally specified here. So Oracle Text is indexing columns that haven't been specified (although they are defined in a preference attribute/passed in the PARAMETERS part of the CREATE INDEX statement).

Oracle Text/Intermedia is actually an implementation of an Oracle Data Cartridge. Oracle Text is an object that implements the Oracle Extensibility Interface. One method in this interface is ODCIIndexUpdate.

So by creating a dummy/fake column to build your index on and then updating this row, or implementing a trigger as you have done Todd, you are ensuring that the ROWID always be passed through the extensibility interface when any a, b, or c, or the fake column are updated.

Hexathioorthooxalate

"Todd Barry" <toddbarry_at_earthlink.net> wrote in message news:5i6trv8cfvfci2c0rvngsv3gmasiclibph_at_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 Sat Nov 22 2003 - 16:01:00 CST

Original text of this message

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