OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Materialized View (Oracle 11g-Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized View [message #646474]

Thu, 31 December 2015 11:39

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.

Report message to a moderator

Re: Materialized View [message #646476 is a reply to message #646474]

Thu, 31 December 2015 12:05

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Materialized View [message #646482 is a reply to message #646474]

Thu, 31 December 2015 23:05

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;

Report message to a moderator

Re: Materialized View [message #646483 is a reply to message #646482]

Thu, 31 December 2015 23:49

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Materialized View [message #646490 is a reply to message #646482]

Fri, 01 January 2016 11:42

John Watson
Messages: 8960
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

Report message to a moderator

Previous Topic:	use_concat hint
Next Topic:	Insert from Select - Performance Issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 22:12:23 CST 2024