| Materialized view having oracle text index not returning results after complete refresh [message #633882] |
Thu, 26 February 2015 12:39  |
 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
