0

I have a weird problem while creating a materialized view on a table with alot of columns, indexes and constraints.

I tried running the command:

CREATE MATERIALIZED VIEW mvX PARALLEL BUILD IMMEDIATE REFRESH FAST AS SELECT * FROM table_name

After running the command I got: "cannot create a fast refresh materialized view from a complex query" So I run the command: dbms_mview.explain_mview('SELECT * FROM table_name') and checked the mv_capabilities_table at REFRESH_FAST_AFTER_INSERT, there was the message: "inline view or subquery in FROM list not supported for this type MV"

  1. I have created a matiralized view log on the table with primary key and rowid.
  2. I tried to create the same matiralized view on a copy table (CREATE TABLE copy_table_name as select * from table_name) and it finish successfully (created a log on this table too)

This view must be FAST REFRESH. I can't explain why on the copy table it succeeded and on the original table it failed. help?

user967710
  • 1,815
  • 3
  • 32
  • 58
  • 1
    Sounds like this table is actually a view. – David Aldridge Jul 01 '13 at 13:53
  • No. it's not. Definitley a table. – user967710 Jul 01 '13 at 15:48
  • You'd best show all the code by which you create the mv, test the capabilities, and show that it's a table not a view then I think. – David Aldridge Jul 01 '13 at 16:14
  • create : CREATE MATERIALIZED VIEW mvX PARALLEL BUILD IMMEDIATE REFRESH FAST AS SELECT * FROM table_name test: call dbms_mview.explain_mview('SELECT * FROM table_name') and select * from mv_capabilities_table. type of object : select object_type from user_objects where object_name = 'TABLE_NAME'; (return TABLE) – user967710 Jul 02 '13 at 07:32

1 Answers1

0

Is it possible that one of the columns in your table is a computed/virtual column? If there is then I would investigate the possiblity that it may not work in that case. Its the only reason I can think why the MV Capabilities would give such an error in your case.

Tomás
  • 535
  • 6
  • 23