Home » RDBMS Server » Performance Tuning » Materialized View (Oracle 11g-Windows)
Materialized View [message #646474] Thu, 31 December 2015 11:39 Go to next message
arunOracle
Messages: 2
Registered: December 2015
Location: India
Junior Member
Hi,
I'm trying to create materialized view for complex that include inner join clause in the query ,and this view should automatically gets updated(refresh fast) with the data matching this condition for every new data committed in database level.
I'm trying this approach to increase a performance measure when it comes to large data model(100+ tables).
And even i have create materialized view log for all the table with ROWID,PRIMARY KEY..

Sample format:
create materialized view test_view build immediate refresh fast on commit as
SELECT INTTABLE.COLUMN1 AS "init col",
TABLE1.COLUMN1 AS "tab1 col" ,
TABLE2.COLUMN1 AS "tab2 col",
TABLE3.COLUMN1 AS "tab3 col"
FROM TABLE1
INNER JOIN TABLE3
ON TABLE1.COLUMN1 = TABLE3.COLUMN2
INNER JOIN TABLE2
ON TABLE2.COLUMN1 = TABLE3.COLUMN3
INNER JOIN INTTABLE
ON INTTABLE.COLUMN1 = TABLE2.COLUMN2


Error Message:
Error at Command Line : 11 Column : 12
Error report -
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 - "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
commit time.
*Action: Specify only valid options.
Re: Materialized View [message #646476 is a reply to message #646474] Thu, 31 December 2015 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Cause: The materialized view did not satisfy conditions for refresh at commit time.


What do you expect from us?
Try DBMS_MVIEW.EXPLAIN_MVIEW on it and see what Oracle tells you.

Re: Materialized View [message #646482 is a reply to message #646474] Thu, 31 December 2015 23:05 Go to previous messageGo to next message
arunOracle
Messages: 2
Registered: December 2015
Location: India
Junior Member
I would like to create a materialized view that should build immediate and refresh fast on every commit in the database for the below inner join query.

Query:
SELECT INTTABLE.COLUMN1 AS "init col",
TABLE1.COLUMN1 AS "tab1 col" ,
TABLE2.COLUMN1 AS "tab2 col",
TABLE3.COLUMN1 AS "tab3 col"
FROM TABLE1
INNER JOIN TABLE3
ON TABLE1.COLUMN1 = TABLE3.COLUMN2
INNER JOIN TABLE2
ON TABLE2.COLUMN1 = TABLE3.COLUMN3
INNER JOIN INTTABLE
ON INTTABLE.COLUMN1 = TABLE2.COLUMN2;

Re: Materialized View [message #646483 is a reply to message #646482] Thu, 31 December 2015 23:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 31 December 2015 19:05

Quote:
Cause: The materialized view did not satisfy conditions for refresh at commit time.


What do you expect from us?
Try DBMS_MVIEW.EXPLAIN_MVIEW on it and see what Oracle tells you.

Re: Materialized View [message #646490 is a reply to message #646482] Fri, 01 January 2016 11:42 Go to previous message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
Your release of Oracle is critical here - ANSI join syntax is not fully implemented for materialized views. Try re-writing the query to use the old join syntax, using filters in the predicate.

And to do the moderator bit:

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
Previous Topic: use_concat hint
Next Topic: Insert from Select - Performance Issue
Goto Forum:
  


Current Time: Sat Feb 24 09:11:28 CST 2018

Total time taken to generate the page: 0.01360 seconds