Home » Server Options » Text & interMedia » Oracle Text Index Sync Problem on Update (11g)
Oracle Text Index Sync Problem on Update [message #591108] Wed, 24 July 2013 18:14 Go to next message
raman.kashyap
Messages: 9
Registered: July 2013
Junior Member
Hi Team - I'm trying to use Multi_column_datastore with basic_lexer to create an oracle text index. Everything works as expected but for some unknown reasons, indexes are not being synced for a couple of columns in the same table. I noticed that they were of CLOB type. I'm using Sync (on commit) method to refresh indexes on every commit. Am I missing something here?
Please suggest.

Thanks,
Raman

[Updated on: Wed, 24 July 2013 18:14]

Report message to a moderator

Re: Oracle Text Index Sync Problem on Update [message #591111 is a reply to message #591108] Wed, 24 July 2013 20:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Synchronization will only occur whenever the column that the index was created on is updated, not when any of the columns in the multi_column_datastore are updated. So, you either need to make sure that you update the indexed column as part of whatever process updates the other columns or write a trigger to do so automatically.
Re: Oracle Text Index Sync Problem on Update [message #591382 is a reply to message #591111] Sat, 27 July 2013 12:20 Go to previous messageGo to next message
raman.kashyap
Messages: 9
Registered: July 2013
Junior Member
Hi Barbara - Thanks for your reply. I understand what you are saying. But in my case, if I'm updating value for my clob columns, which are a part of other columns in multi-column-datastore type of index, the index is not getting refreshed. So, to troubleshoot this problem, I have created a new index only for a single clob column, and now if I update this clob value, this new index gets refreshed, but not the original one.
I can paste some of my code here, if it can help understand the problem.

Thanks
Raman
Re: Oracle Text Index Sync Problem on Update [message #591383 is a reply to message #591382] Sat, 27 July 2013 13:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please paste the code.
Re: Oracle Text Index Sync Problem on Update [message #591384 is a reply to message #591383] Sat, 27 July 2013 15:13 Go to previous messageGo to next message
raman.kashyap
Messages: 9
Registered: July 2013
Junior Member
This is my table DDL:
CREATE TABLE "INTERACTIONS"
(
	"ID" varchar2(36),
	"SECTIONTITLE" CLOB,
	"PRESENTATIONTITLE" VARCHAR2(1000 CHAR),
	"SUGGESTEDACTIONS" CLOB,
	"PRESENTATIONDATE" TIMESTAMP (6),
	"ABSTRACT" CLOB,
	"AUTHOR" VARCHAR2(255 CHAR), 
	"SEARCH" VARCHAR2(1 CHAR),
	"CREATEDBY" VARCHAR2(255 CHAR), 
	"CREATEDDATE" TIMESTAMP (6), 
	"MODIFIEDBY" VARCHAR2(255 CHAR), 
	"MODIFIEDDATE" TIMESTAMP (6)
) 
SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
LOB ("SECTIONTITLE") STORE AS BASICFILE (ENABLE STORAGE IN ROW CHUNK 8192 RETENTION  NOCACHE LOGGING  
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))  
LOB ("PASTEABSTRACT") STORE AS BASICFILE (ENABLE STORAGE IN ROW CHUNK 8192 RETENTION  NOCACHE LOGGING  
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));

And, this is my Index code:
BEGIN
CTX_DDL.CREATE_PREFERENCE ('MY_LEXER', 'BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE ('MY_LEXER', 'PRINTJOINS', '`~!@#$%^&*-_?/|');

CTX_DDL.CREATE_PREFERENCE('INTERACTION_SEARCH_DATASTORE', 'MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('INTERACTION_SEARCH_DATASTORE', 'COLUMNS', 'SECTIONTITLE, PRESENTATIONTITLE, SUGGESTEDACTIONS, ABSTRACT, AUTHOR, CREATEDBY, MODIFIEDBY');
END;

CREATE INDEX "INTERACTIONS_SEARCH_IDX" ON "INTERACTIONS" ("SEARCH") INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('LEXER MY_LEXER DATASTORE INTERACTION_SEARCH_DATASTORE SYNC(ON COMMIT)');


You can see, the columns: SECTIONTITLE, SUGGESTEDACTIONS, & ABSTRACT are CLOB fields, and other are VARCHAR2 fields.
If I update an existing row with any of the VARCHAR2 fields, my index get refreshed with new values. But when I update my CLOB fields, then it does not.

And If I insert a new row, then my index always get refreshed, which is perfect.

[Updated on: Sat, 27 July 2013 15:16]

Report message to a moderator

Re: Oracle Text Index Sync Problem on Update [message #591385 is a reply to message #591384] Sat, 27 July 2013 15:19 Go to previous messageGo to next message
raman.kashyap
Messages: 9
Registered: July 2013
Junior Member
And this single column INDEX works as expected for CLOB fields. It always sync the updates.
CREATE INDEX "INTERACTION_TITLE_IDX" ON "TRIP_INTERACTION" ("SECTIONTITLE") INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('LEXER LEXER SYNC ( ON COMMIT)');
Re: Oracle Text Index Sync Problem on Update [message #591386 is a reply to message #591385] Sat, 27 July 2013 19:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I have understood your problem from the beginning. It is obvious that you have not understood my previous explanation, even though you think you have. Hopefully, it will be clearer using your own table and columns and datastore and index with a demonstration. Your index is created on the search column, so synchronization occurs only when the search column is updated, even if the value of the search column is not changed. In the following, I have started with a simplified version of your table, datastore, and index, and added some data for testing. I have done one update that updates all of the "oldvalue" to "newvalue", but does not update the search column. The resulting reproduction of your problem is that the index is not synchronized. Then I have done an additional update that updates the search column from null to null, which causes synchronization. So, as previously stated, you need to either update the search column as part of any update to any of the columns in your multi_column_datastore or create a trigger to do so automatically. Please see the demonstration below.

-- simplification of table and multi_column_datastore and index with sync(on commit) that you provided:
SCOTT@orcl12c_11gR2> CREATE TABLE "INTERACTIONS"
  2  (
  3  	     "ID" varchar2(36),
  4  	     "SECTIONTITLE" CLOB,
  5  	     "PRESENTATIONTITLE" VARCHAR2(1000 CHAR),
  6  	     "SUGGESTEDACTIONS" CLOB,
  7  	     "PRESENTATIONDATE" TIMESTAMP (6),
  8  	     "ABSTRACT" CLOB,
  9  	     "AUTHOR" VARCHAR2(255 CHAR),
 10  	     "SEARCH" VARCHAR2(1 CHAR),
 11  	     "CREATEDBY" VARCHAR2(255 CHAR),
 12  	     "CREATEDDATE" TIMESTAMP (6),
 13  	     "MODIFIEDBY" VARCHAR2(255 CHAR),
 14  	     "MODIFIEDDATE" TIMESTAMP (6)
 15  )
 16  /

Table created.

SCOTT@orcl12c_11gR2> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE
  3  	 ('INTERACTION_SEARCH_DATASTORE',
  4  	  'MULTI_COLUMN_DATASTORE');
  5    CTX_DDL.SET_ATTRIBUTE
  6  	 ('INTERACTION_SEARCH_DATASTORE',
  7  	  'COLUMNS',
  8  	  'SECTIONTITLE, PRESENTATIONTITLE, SUGGESTEDACTIONS, ABSTRACT, AUTHOR, CREATEDBY, MODIFIEDBY');
  9  END;
 10  /

PL/SQL procedure successfully completed.

SCOTT@orcl12c_11gR2> CREATE INDEX "INTERACTIONS_SEARCH_IDX"
  2  ON "INTERACTIONS" ("SEARCH")
  3  INDEXTYPE IS "CTXSYS"."CONTEXT"
  4  PARAMETERS
  5    ('DATASTORE INTERACTION_SEARCH_DATASTORE
  6  	 SYNC(ON COMMIT)')
  7  /

Index created.


-- data for testing:
SCOTT@orcl12c_11gR2> INSERT INTO interactions
  2  VALUES
  3    ('a', 'oldvalue', 'oldvalue', 'oldvalue', systimestamp, 'oldvalue', 'oldvalue',
  4  	null, 'oldvalue', systimestamp, 'oldvalue', systimestamp)
  5  /

1 row created.

SCOTT@orcl12c_11gR2> COMMIT
  2  /

Commit complete.

SCOTT@orcl12c_11gR2> SELECT * FROM interactions WHERE CONTAINS (search, 'oldvalue') > 0
  2  /

ID
------------------------------------
SECTIONTITLE
--------------------------------------------------------------------------------
PRESENTATIONTITLE
--------------------------------------------------------------------------------
SUGGESTEDACTIONS
--------------------------------------------------------------------------------
PRESENTATIONDATE
---------------------------------------------------------------------------
ABSTRACT
--------------------------------------------------------------------------------
AUTHOR
--------------------------------------------------------------------------------
S
-
CREATEDBY
--------------------------------------------------------------------------------
CREATEDDATE
---------------------------------------------------------------------------
MODIFIEDBY
--------------------------------------------------------------------------------
MODIFIEDDATE
---------------------------------------------------------------------------
a
oldvalue
oldvalue
oldvalue
27-JUL-13 05.50.23.106000 PM
oldvalue
oldvalue

oldvalue
27-JUL-13 05.50.23.106000 PM
oldvalue
27-JUL-13 05.50.23.106000 PM


1 row selected.


-- update that does not update the indexed search column and therefore does not trigger synchronization,
-- reproducing your problem:
SCOTT@orcl12c_11gR2> UPDATE interactions
  2  SET    sectiontitle = 'newvalue',
  3  	    presentationtitle = 'newvalue',
  4  	    suggestedactions = 'newvalue',
  5  	    abstract = 'newvalue',
  6  	    author = 'newvalue',
  7  	    createdby = 'newvalue',
  8  	    modifiedby = 'newvalue'
  9  WHERE  id = 'a'
 10  /

1 row updated.

SCOTT@orcl12c_11gR2> COMMIT
  2  /

Commit complete.

SCOTT@orcl12c_11gR2> SELECT * FROM interactions WHERE CONTAINS (search, 'newvalue') > 0
  2  /

no rows selected


-- update that updates search column and therefore triggers synchronization of previously updated values:
SCOTT@orcl12c_11gR2> UPDATE interactions
  2  SET    search = null
  3  WHERE  id = 'a'
  4  /

1 row updated.

SCOTT@orcl12c_11gR2> COMMIT
  2  /

Commit complete.

SCOTT@orcl12c_11gR2> SELECT * FROM interactions WHERE CONTAINS (search, 'newvalue') > 0
  2  /

ID
------------------------------------
SECTIONTITLE
--------------------------------------------------------------------------------
PRESENTATIONTITLE
--------------------------------------------------------------------------------
SUGGESTEDACTIONS
--------------------------------------------------------------------------------
PRESENTATIONDATE
---------------------------------------------------------------------------
ABSTRACT
--------------------------------------------------------------------------------
AUTHOR
--------------------------------------------------------------------------------
S
-
CREATEDBY
--------------------------------------------------------------------------------
CREATEDDATE
---------------------------------------------------------------------------
MODIFIEDBY
--------------------------------------------------------------------------------
MODIFIEDDATE
---------------------------------------------------------------------------
a
newvalue
newvalue
newvalue
27-JUL-13 05.50.23.106000 PM
newvalue
newvalue

newvalue
27-JUL-13 05.50.23.106000 PM
newvalue
27-JUL-13 05.50.23.106000 PM


1 row selected.

Re: Oracle Text Index Sync Problem on Update [message #591387 is a reply to message #591386] Sat, 27 July 2013 20:47 Go to previous messageGo to next message
raman.kashyap
Messages: 9
Registered: July 2013
Junior Member
Ohh.. First, thanks a lot for putting such an effort in demonstrating me the actual problem and solution. I got that the index was created on "Search" column, so Search field needs to be updated to refresh index data. I have got the clear picture now.

But now, I have a couple of confusions here:
1) I didn't create the Search column, it was created automatically when I executed the multi_column_datastore preferences statements before creating an index. I added that column here in the create table ddl, to avoid any confusion.
2) Now it makes sense to say that the index should not be refreshed if search field is not updated, but why does it work for all varchar2 fields?
Re: Oracle Text Index Sync Problem on Update [message #591388 is a reply to message #591387] Sat, 27 July 2013 21:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Quote:

1) I didn't create the Search column, it was created automatically when I executed the multi_column_datastore preferences statements before creating an index. I added that column here in the create table ddl, to avoid any confusion.


The search column does not get automatically created. Somebody had to have created it, unless you have some procedure that you are using to create the multi_column_datastore preference that does this.

Quote:

2) Now it makes sense to say that the index should not be refreshed if search field is not updated, but why does it work for all varchar2 fields?


It doesn't synchronize the varchar2 columns if the search column is not updated. If you simply select from the table, then the data, in both varchar2 and clob columns is there, but the index is not synchronized. My previous demo showed that the varchar2 columns were not synchronized without updating the search column. If you think this is happening, then you need to provide a copy and paste of a run of a test case that shows this.




Re: Oracle Text Index Sync Problem on Update [message #591389 is a reply to message #591388] Sat, 27 July 2013 21:12 Go to previous message
raman.kashyap
Messages: 9
Registered: July 2013
Junior Member
I got your point, Barbara. Thanks a lot once again for your time and your help. I'm a developer doing all Microsoft technologies stuff, and all new to Oracle. I really appreciate that you put a such an effort in explaining me all about my problem and solution. This was my first topic on orafaq site and I'm completely satisfied with your answers.

-Thanks again,
-Raman
Previous Topic: Fuzzy Matching Using Contains
Next Topic: Oracle 11.2 text search using contains operator for % wildcard
Goto Forum:
  


Current Time: Thu Mar 28 13:13:46 CDT 2024