Materialized View

[Viking]

I have a view and would like an index (key) on it, but we can't create an index on a view.

I was reading about materialized views where you create a table from an SQL statement and you can create an index on that.

So, can I create a (materialized view) table with a select of the data from a view, and then create an index on that materialized view?
Essentially, assuming I already have a view called myView which selects some data from various physical files, I would like to do the following:

-- Create my materialized view
CREATE TABLE MyMaterializedView AS
SELECT myField1, MyField2, MyField3
FROM myView

-- Create an index on my materialized view
CREATE INDEX IDX_MYFIELD1 ON MyMaterializedView (myField1)

But it doesn't seem to work... it doesn't seem to like the create table this way.

What am I missing? Would appreciate if someone could shed some light on this. Thanks.

[B.Hauser]

The Syntax for creating a MQT looks like this:

Create or Replace Table YourSchema/yourMQT]
   As (Select ….)
Data Initially Immediate
Refresh Deferred
Maintained by Use
Enable Query Optimization

... but keep in mind MQTs are currently not automatically updated, i.e. you have to refresh them manually with the REFRESH SQL Command, so there can be a gap between the real data and the data in the MQT.

You need to create indexes on each table/physical file used in your view.
Normally it performs well. .... but without having seen your SQL statement it is hard to advise which indexes should be created or available.