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  |
 |
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 #591382 is a reply to message #591111] |
Sat, 27 July 2013 12:20  |
|
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 #591384 is a reply to message #591383] |
Sat, 27 July 2013 15:13 |
|
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 #591386 is a reply to message #591385] |
Sat, 27 July 2013 19:59 |
 |
Barbara Boehmer
Messages: 9101
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 #591388 is a reply to message #591387] |
Sat, 27 July 2013 21:04 |
|
Barbara Boehmer
Messages: 9101
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 |
|
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
|
|
|
|
Goto Forum:
Current Time: Fri Dec 27 00:21:38 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
