Can I create materialized view on a view? 1

[fiat2]

using Oracle 9.
I'm trying to create a materialized view on a view but keep getting the following error.

The view does exist. I'm under the assumption that creating a materialized view on a view is done the same way as on a table.

ora-00942: table or view does not exist.

CREATE MATERIALIZED VIEW LOG ON VW_VIEW
TABLESPACE CORESDATA
PCTUSED    30
PCTFREE    60
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID
EXCLUDING NEW VALUES;

 

[Dagon]

You're not trying to create a materialized view - you're trying to create a materialized view log, which is entirely different.

It doesn't make any sense to create a materialized view log on a view because the materialized view log needs to keep track of the changes in a table using its ROWID, which a view doesn't have.

You should be able to create a materialized view on a view, although you wouldn't be able to fast refresh it.

 

[fiat2]

Thanks for pointing out my obvious mistake...and more importantly, not doing so in an obnoxious manner.
I greatly appreciate that.

After your post, I attempted to create the MV on the reporting database and receive the following error:

ora-23413: table "" does not have a materialized view log

I would like to have view data static on the reporting server to decrease query time....

 

[fiat2]

Here is what I'm thinking now...

Create the materialized view logs on the tables that I want the data from....

Then create a materialized view on these tables with a more complex query then usual..

 

[Dagon]

You would probably get ora-23413 if you were trying to create the Mview that can be fast refreshed. You will need to create materialized viwe logs on the base tables if you want to do that.

What sort of query do you want to put in the Mview ? Not all types of queries will allow fast refreshes.

 

[fiat2]

As you can probably see, my knowledge of materialized views is limited.

I just did some research on Refresh types...

I'm gonna go back and work on this solution for a while and either post more questions or explain what I did.

Thanks again for your assistance.