Home » Server Options » Text & interMedia » Materialized view having oracle text index not returning results after complete refresh
Materialized view having oracle text index not returning results after complete refresh [message #633882] Thu, 26 February 2015 12:39 Go to next message
akshay160389
Messages: 6
Registered: February 2015
Location: India
Junior Member
Hi,

I have a created a materialized view on multiple tables having refresh mode to be "Complete Refresh" with refresh interval of 5 mins for the mview.

CREATE MATERIALIZED VIEW MV_TEST
NOLOGGING
CACHE
BUILD IMMEDIATE
refresh complete start with (sysdate) next (sysdate+ 5/1440) with rowid USING TRUSTED CONSTRAINTS
AS
select t1.col1, t1.col2,
t2.col1,.......t10.col3
from table1 t1, table2 t2,......
table10 t10
where t1.some_field = t2.some_field, .......
t10.some_field=t9.some_field;

Then, an oracle text index of CONTEXT type is created on the mview having periodic sync of 5 mins and multi_column_datastore preference.

Create index trial_in_ora_index on MV_TEST (description)
indextype is ctxsys.context
parameters ( '
datastore multiCol_inv
section group auto_trial
sync (every "sysdate+(5/24/60)")');

Behavior observed :
After around 5 minutes, when the mview/index is getting refreshed, the results are not fetched. Is this behavior expected?
Querying mview without the index returns results but not with index, that is

Select * from MV_TEST where description like '%ale%';--- returns results

Select * from MV_TEST where contains(description, '%ale%' ) > 0; --- doesn't return results.

Meanwhile on updating one of the records in the base table reflected the changes on mview after given refresh interval time. But the query with index does not return results.

After waiting for a long time, when i try to drop the index, it sometimes errors out saying : SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. Finally after dropping and recreating the index, everything seems to work fine.

What can be the cause of such behavior and is there a solution to the above issue? Is anything being missed from implementation point.

Note: I cannot use triggers, and all these once set should auto refresh regularly in sync with updated changes from front-end.
Also, i am new to mviews and oracle text.

Thanks
Akshay

[Updated on: Thu, 26 February 2015 12:41]

Report message to a moderator

Re: Materialized view having oracle text index not returning results after complete refresh [message #633893 is a reply to message #633882] Fri, 27 February 2015 02:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
What is your Oracle version?

Please post the results of:
SELECT CTX_REPORT.CREATE_INDEX_SCRIPT ('trial_in_ora_index') FROM DUAL;


Re: Materialized view having oracle text index not returning results after complete refresh [message #633894 is a reply to message #633893] Fri, 27 February 2015 03:05 Go to previous messageGo to next message
akshay160389
Messages: 6
Registered: February 2015
Location: India
Junior Member
Hi Barbara,

Thanks for replying.
Oracle version is 11g.
PFB the results of the ctx_report.

begin
ctx_ddl.create_preference('"TRIAL_IN_ORA_INDEX_DST"','MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('"TRIAL_IN_ORA_INDEX_DST"','COLUMNS',' BusinessID, INVOICE_NUM, party_name, party_number,
JGZZ_FISCAL_CODE,
INVOICE_DATE,
INVOICE_CURRENCY_CODE,
PARTY_SITE_NAME,
ADDRESS1, ADDRESS2,
DESCRIPTION,
PAYMENT_CURRENCY_CODE,
NAME');
end;
/

begin
ctx_ddl.create_preference('"TRIAL_IN_ORA_INDEX_FIL"','NULL_FILTER');
end;
/

begin
ctx_ddl.create_section_group('"TRIAL_IN_ORA_INDEX_SGP"','AUTO_SECTION_GROUP');
end;
/

begin
ctx_ddl.create_preference('"TRIAL_IN_ORA_INDEX_LEX"','BASIC_LEXER');
ctx_ddl.set_attribute('"TRIAL_IN_ORA_INDEX_LEX"','INDEX_THEMES','NO');
end;
/

begin
ctx_ddl.create_preference('"TRIAL_IN_ORA_INDEX_WDL"','BASIC_WORDLIST');
ctx_ddl.set_attribute('"TRIAL_IN_ORA_INDEX_WDL"','STEMMER','ENGLISH');
ctx_ddl.set_attribute('"TRIAL_IN_ORA_INDEX_WDL"','FUZZY_MATCH','GENERIC');
end;
/

begin
ctx_ddl.create_stoplist('"TRIAL_IN_ORA_INDEX_SPL"','BASIC_STOPLIST');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','Corp');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','Mr');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','Mrs');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','Ms');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','Mz');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','a');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','about');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','after');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','all');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','also');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','an');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','and');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','any');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','are');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','as');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','at');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','be');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','because');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','been');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','but');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','by');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','can');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','co');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','could');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','for');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','from');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','had');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','has');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','have');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','he');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','her');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','his');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','if');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','in');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','inc');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','into');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','is');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','it');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','its');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','last');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','more');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','most');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','no');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','not');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','of');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','on');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','one');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','only');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','or');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','other');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','out');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','over');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','s');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','says');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','she');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','so');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','some');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','such');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','than');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','that');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','the');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','their');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','there');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','they');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','this');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','to');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','up');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','was');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','we');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','were');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','when');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','which');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','who');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','will');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','with');
ctx_ddl.add_stopword('"TRIAL_IN_ORA_INDEX_SPL"','would');
end;
/

begin
ctx_ddl.create_preference('"TRIAL_IN_ORA_INDEX_STO"','BASIC_STORAGE');
ctx_ddl.set_attribute('"TRIAL_IN_ORA_INDEX_STO"','R_TABLE_CLAUSE','lob (data) store as (cache)');
ctx_ddl.set_attribute('"TRIAL_IN_ORA_INDEX_STO"','I_INDEX_CLAUSE','compress 2');
end;
/


begin
ctx_output.start_log('TRIAL_IN_ORA_INDEX_LOG');
end;
/

create index "FUSION"."TRIAL_IN_ORA_INDEX"
on "FUSION"."MV_INVOICE_TEST"
("DESCRIPTION")
indextype is ctxsys.context
parameters('
datastore "TRIAL_IN_ORA_INDEX_DST"
filter "TRIAL_IN_ORA_INDEX_FIL"
section group "TRIAL_IN_ORA_INDEX_SGP"
lexer "TRIAL_IN_ORA_INDEX_LEX"
wordlist "TRIAL_IN_ORA_INDEX_WDL"
stoplist "TRIAL_IN_ORA_INDEX_SPL"
storage "TRIAL_IN_ORA_INDEX_STO"
sync (every "SYSDATE+(5/24/60)" memory 288358400)
')
/

begin
ctx_output.end_log;
end;
/

[Updated on: Fri, 27 February 2015 03:10]

Report message to a moderator

Re: Materialized view having oracle text index not returning results after complete refresh [message #633899 is a reply to message #633882] Fri, 27 February 2015 03:42 Go to previous messageGo to next message
John Watson
Messages: 7150
Registered: January 2010
Location: Global Village
Senior Member
I know very little about CONTEXT, please correct me anyone if I am wrong. However, as your refresh method is COMPLETE, is it appropriate to have a periodic SYNC job? Every five minutes all the rows in the MV are being replaced, so you might as well rebuild the index from nothing. Is there some option to force this? Furthermore, even though the MV and the index are being refreshed with equal frequency, the jobs will not launching at the same time: I can see that this might cause your ORA-00054.

Re: Materialized view having oracle text index not returning results after complete refresh [message #633903 is a reply to message #633899] Fri, 27 February 2015 04:01 Go to previous messageGo to next message
akshay160389
Messages: 6
Registered: February 2015
Location: India
Junior Member
Hi John,

Actually even after i remove the periodic sync option from the index, i am getting the same issue.
The results from the second sql(the one using text index) does not fetch results even though the mview has results in it.
All this happens after first complete refresh of mview.
Re: Materialized view having oracle text index not returning results after complete refresh [message #633907 is a reply to message #633903] Fri, 27 February 2015 04:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
I am able to get results by changing the index synchronization parameter to either:

sync (on commit)

or

sync (every "sysdate+(6/1440)")

Since I only tested with a few rows, you may need to increase the interval and/or wait longer for it to finish. I would recommend sync(on commit) instead of the interval.

Re: Materialized view having oracle text index not returning results after complete refresh [message #633908 is a reply to message #633907] Fri, 27 February 2015 04:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
Here is one of my simplified tests. Note that I increased the interval for the index synchronization to make it longer than the interval for the materialized view refresh and I used dbms_lock.sleep to wait long enough after committing the new data to make sure that both materialized view refresh and index synchronization had completed. Your actual view has multiple tables and more rows, so everything will take a lot longer.

SCOTT@orcl12c> CREATE TABLE table1
  2    (col1	     VARCHAR2(15),
  3  	col2	     VARCHAR2(15),
  4  	description  VARCHAR2(15))
  5  /

Table created.

SCOTT@orcl12c> INSERT ALL
  2  INTO table1 VALUES ('ale1', 'ale1', 'ale1')
  3  INTO table1 VALUES ('ale2', null, null)
  4  INTO table1 VALUES (null, 'ale3', null)
  5  INTO table1 VALUES ('other', 'data', null)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl12c> CREATE MATERIALIZED VIEW LOG ON table1 WITH ROWID
  2  /

Materialized view log created.

SCOTT@orcl12c> CREATE MATERIALIZED VIEW MV_TEST
  2  NOLOGGING
  3  CACHE
  4  BUILD IMMEDIATE
  5  refresh complete start with (sysdate) next (sysdate+ 5/1440)
  6  with rowid USING TRUSTED CONSTRAINTS
  7  AS
  8  select t1.col1, t1.col2, t1.description
  9  from table1 t1
 10  /

Materialized view created.

SCOTT@orcl12c> begin
  2    ctx_ddl.create_preference ('multicol_inv', 'MULTI_COLUMN_DATASTORE');
  3    ctx_ddl.set_attribute ('multicol_inv', 'columns', 'col1, col2');
  4    ctx_ddl.create_section_group ('auto_trial', 'auto_section_group');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl12c> Create index trial_in_ora_index on MV_TEST (description)
  2  indextype is ctxsys.context
  3  parameters ( '
  4  datastore multiCol_inv
  5  section group auto_trial
  6  sync (every "sysdate+(6/1440)")')
  7  /

Index created.

SCOTT@orcl12c> INSERT ALL
  2  INTO table1 VALUES ('ale4', 'ale4', 'ale4')
  3  INTO table1 VALUES ('ale5', null, null)
  4  INTO table1 VALUES (null, 'ale6', null)
  5  INTO table1 VALUES ('something', 'else', null)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl12c> COMMIT
  2  /

Commit complete.

SCOTT@orcl12c> EXEC DBMS_LOCK.SLEEP (420)

PL/SQL procedure successfully completed.

SCOTT@orcl12c> SELECT * FROM mv_test
  2  /

COL1            COL2            DESCRIPTION
--------------- --------------- ---------------
ale1            ale1            ale1
ale2
                ale3
other           data
ale4            ale4            ale4
ale5
                ale6
something       else

8 rows selected.

SCOTT@orcl12c> Select * from MV_TEST where contains(description, '%ale%' ) > 0
  2  /

COL1            COL2            DESCRIPTION
--------------- --------------- ---------------
ale1            ale1            ale1
ale2
                ale3
ale4            ale4            ale4
ale5
                ale6

6 rows selected.



Re: Materialized view having oracle text index not returning results after complete refresh [message #633910 is a reply to message #633908] Fri, 27 February 2015 05:45 Go to previous messageGo to next message
akshay160389
Messages: 6
Registered: February 2015
Location: India
Junior Member
Thank you so much Barbara for such explaination.

But when i run the scripts given by you,
results are fetched for the query that makes use of text index, but not always.

Whereas, the updated results are being shown in mview for col1, col2 and description.

Is it because of refreshing the index, the results are not being shown.
If so, can i somehow overcome this situation as i want results to appear always(cannot have 0 results).

P.S. - Want the results to be fetched always, even though updated ones can appear after sometime, but there should not be any lag between results.



[Updated on: Fri, 27 February 2015 05:54]

Report message to a moderator

Re: Materialized view having oracle text index not returning results after complete refresh [message #633927 is a reply to message #633910] Fri, 27 February 2015 20:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
The usual method is to use REFRESH FAST ON COMMIT for the materialized view and SYNC(ON COMMIT) for the context index. In the demonstration below, I made a very large insert before committing and did not use dbms_lock.sleep to wait and yet it returned all 6 of the rows. While it was still re-indexing, I ran the query from another session and it still returned the original 3 rows. There was no problem with returning 0 rows in either session.

SCOTT@orcl12c> CREATE TABLE table1
  2    (col1	     VARCHAR2(30),
  3  	col2	     VARCHAR2(30),
  4  	description  VARCHAR2(30))
  5  /

Table created.

SCOTT@orcl12c> INSERT ALL
  2  INTO table1 VALUES ('Barbara1', 'Barbara1', 'Barbara1')
  3  INTO table1 VALUES ('Barbara2', null, null)
  4  INTO table1 VALUES (null, 'Barbara3', null)
  5  INTO table1 VALUES ('other', 'data', null)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl12c> CREATE MATERIALIZED VIEW LOG ON table1 WITH ROWID
  2  /

Materialized view log created.

SCOTT@orcl12c> CREATE MATERIALIZED VIEW mv_test
  2  NOLOGGING
  3  CACHE
  4  BUILD IMMEDIATE
  5  refresh fast on commit
  6  with rowid USING TRUSTED CONSTRAINTS
  7  AS
  8  select t1.col1, t1.col2, t1.description
  9  from table1 t1
 10  /

Materialized view created.

SCOTT@orcl12c> begin
  2    ctx_ddl.create_preference ('multicol_inv', 'MULTI_COLUMN_DATASTORE');
  3    ctx_ddl.set_attribute ('multicol_inv', 'columns', 'col1, col2');
  4    ctx_ddl.create_section_group ('auto_trial', 'auto_section_group');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl12c> Create index trial_in_ora_index on mv_test (description)
  2  indextype is ctxsys.context
  3  parameters ( '
  4  datastore multiCol_inv
  5  section group auto_trial
  6  sync (on commit)')
  7  /

Index created.

SCOTT@orcl12c> INSERT INTO table1 (col1, col2, description)
  2  SELECT owner, object_name, subobject_name
  3  FROM   all_objects
  4  /

89843 rows created.

SCOTT@orcl12c> INSERT ALL
  2  INTO table1 VALUES ('Barbara4', 'Barbara4', 'Barbara4')
  3  INTO table1 VALUES ('Barbara5', null, null)
  4  INTO table1 VALUES (null, 'Barbara6', null)
  5  INTO table1 VALUES ('something', 'else', null)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl12c> COMMIT
  2  /

Commit complete.

SCOTT@orcl12c> Select * from mv_test where contains(description, 'Barbara%' ) > 0
  2  /

COL1                           COL2                           DESCRIPTION
------------------------------ ------------------------------ ------------------------------
Barbara1                       Barbara1                       Barbara1
Barbara2
                               Barbara3
Barbara4                       Barbara4                       Barbara4
Barbara5
                               Barbara6

6 rows selected.

Re: Materialized view having oracle text index not returning results after complete refresh [message #633966 is a reply to message #633927] Sun, 01 March 2015 23:24 Go to previous messageGo to next message
akshay160389
Messages: 6
Registered: February 2015
Location: India
Junior Member
Hi Barbara,

The use case that you have sent is working perfectly(having refresh fast on commit for mview and sync on commit for index).
But, as per our standard guidelines given by FRE team, we're not allowed to use sync on commit for text indexes. Sad

Also, to use refresh fast on commit for mviews, we should be having materialized view logs on each of the tables along with their rowIds in the select query.
Now, another issue here is that i have a couple of "views" in my select query.
And on trying to create logs on views, it ends up throwing "The command requires full table access but the user is not SYS and doesn't have the permission to by pass security policies." error.
So i guess i can't use refresh fast mode for mviews.

Re: Materialized view having oracle text index not returning results after complete refresh [message #633969 is a reply to message #633966] Mon, 02 March 2015 02:03 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
If you are not allowed to use sync(on commit) for text indexes, then use whatever interval you are allowed to use. It will just mean that it will be longer before new rows are returned in searches.

You need a materialized view log with rowid on each underlying table involved in the materialized view. So, if there are views in your query that the materialized view is created from, then you need to create the materialized view logs on the underlying tables of those views, not on the views or you could rewrite your query to use just tables and no views. If you don't use refresh fast on commit, then you are going to have the problem with returning no rows sometimes.

One other alternative is to use a user_datastore and a procedure that does a select similar to the materialized view, instead of a materialized view.
Re: Materialized view having oracle text index not returning results after complete refresh [message #634018 is a reply to message #633969] Mon, 02 March 2015 23:17 Go to previous messageGo to next message
akshay160389
Messages: 6
Registered: February 2015
Location: India
Junior Member
Hi Barbara,

Thank you so much for all your help.
As u suggested to create logs with rowId on all the underlying table, i tried that but was unable to do so on all of them because of VPD policies.
We are talking with respective teams if they can allow sync (on commit) on mview as it is directly not on any of the tables.
Finally this is what our final approach would be
To have a mview having complete refresh mode with 5 mins of interval given inline.
And creating a Context type oracle text index with multi_column_datastore preference having sync (on commit) as synchronization technique.

With this i am not able to see abrupt behavior in results and processing seems to be smooth.
Do you see any issue with the above approach.
Re: Materialized view having oracle text index not returning results after complete refresh [message #634064 is a reply to message #634018] Tue, 03 March 2015 14:22 Go to previous message
Barbara Boehmer
Messages: 8751
Registered: November 2002
Location: California, USA
Senior Member
As long as you use sync(on commit) in your text index parameters, I believe that should eliminate the problem with getting 0 rows, but you will have to test and see to be sure. Do you know what their reason is for objecting to sync(on commit) on the text index? The more often that you synchronize a text index, the more fragmented the index becomes, however as long as you are setting the interval on the materialized view, sync(on commit) on the text index will not cause the index to be any more fragmented than if the index were synchronized at the same interval, which, as you know, causes the 0 rows problem. The more often that you refresh your materialized view, the more synchronization that will occur, and the more fragmented the index will become. Index fragmentation will gradually slow down queries. To reduce index fragmentation, you need to periodically either optimize the index or rebuild the index or recreate the index. Which method you choose, how often you do it, and when you do it, depends on your situation. If there is some time when your system is unused, such as each weekend or each night, then you may wish to do index maintenance then. You may need to do some experimentation to see how long it takes for the fragmentation to affect query performance in order to decide how often you need to optimize the index.
Previous Topic: Index creation performance problem with LOB/CLOB content
Next Topic: Accent insensitive indexation
Goto Forum:
  


Current Time: Tue Nov 21 06:09:51 CST 2017

Total time taken to generate the page: 0.18901 seconds