Unable to Create Materialized View

[ronnie]

Hi,
I have a table with 3 million records in it.

The table definition is as follows

SQL> desc ofas
Name Null? Type
----------------------------------------- -------- ---------------
COMPANY VARCHAR2(10)
TIME VARCHAR2(10)
REGION VARCHAR2(10)
LINEITEM VARCHAR2(15)
ACTUAL NUMBER(20,6)
ACTUALUS NUMBER(20,6)
YEAR_QUARTER VARCHAR2(5)

Now I am trying to create a materialized view on this table but it is giving me an error.

This is the syntax I am using to create the view

1 create materialized view company_list
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select distinct company,count(*) from ofas
7* group by company
8 /
elect distinct company,count(*) from ofas
*
EROR at line 6:
OA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Please Suggest

Thanks
Ronnie

[marist89]

Error: ORA-12054
Text: cannot set the ON COMMIT refresh attribute for the materialized view
---------------------------------------------------------------------------
Cause: The materialized view does not satisfy conditions for refresh at
commit time.
Action: Specify only legal options.

Check out http://metalink.oracle.com/metalink/...&p_id=101705.1

I'm guessing the problem is with the DISTINCT portion of your query. In addition, you don't need to select DISTINCT as you are doing a group by...