8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Materialized Views in Oracle


Shahid said...

create materialized view test3 build deferred refresh force
on demand enable query rewrite on prebuilt table as
select *
from test2;

Error: Missing keyword

As per the given syntax by you i have written this query.But i am getting error.please help how to proceed further..

Tim... said...

Hi.

This is not from my article. This is something you have written. Please post your question in the forum, giving the create table statement this is based on, then I will take a look.

Cheers

Tim...

Prathap said...

Very useful

Sayed said...

Good work Tim...

Vikas Katiyar said...

Correct Syntax is below . "ON PREBUILT TABLE" should come first , otherwise "Missing keyword " will occur.

-- Build the materialized view using the existing table segment.
CREATE MATERIALIZED VIEW emp_mv
ON PREBUILT TABLE
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM emp@db1.world;

Tim... said...

Hi.

Yeah, the syntax bit was not great. I've altered it to make it clearer. :)

Cheers

Tim...

João Borges Barreto said...

"increase the work needed to perform DDL on the base table" I think you meant DML and not DDL :-) great article thanks!

Tim... said...

Hi.

Thanks for spotting the typo. I've corrected it now. :)

Cheers

Tim...

Anonymous said...

Hi Tim,

In below query rewrite, per my understanding you should use materialized views instead of the actual table once mv is ready with the aggregated data.

Below is the one showed in article:
---------------
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;

Corrected One:
----
SELECT deptno, SUM(sal)
FROM emp_aggr_mv
GROUP BY deptno;

Please confirm.

Tim... said...

Hi

No. You still write the query against the base table and the optimiser rewrites it to use the materialised view. This way, if the data becomes stale it can revert back to using the table

Cheers

Tim...

Marcin said...

Hello Tim,
My question comes to me like bumerang, its about the value in column dba_registered_mviews.mview_site.
I think that some changes appeared between version 11g and 12c. How the target database set this value, base on which data?

Tim... said...

Hi.

I would expect this to controlled using the DRIVING_SITE hint, but that has been around for a while.

https://oracle-base.com/articles/misc/managing-database-links#performance

Cheers

Tim...

Marcin said...

But my question was about, how the target database know the mview_site, does it get this info from any connection informations or something.
Because right now in 12c every time when the target database is refreshed I have to drop and create mviews from scratch. But in 11g, database automagically was able to connect to the previous refreshed target and did only fast refresh.

Tim... said...

Hi.

If you are expecting a fast refresh from a source object with mview logs, I would expect a replace to be a problem. If it is a full refresh, I would not expect this. Sounds like you need to raise an SR with Oracle Support.

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.