error in creating materialized view
I want to create a materialized view,which uses
Having count(*)>1 it shows error in this line as
HAVING COUNT(*) > 1
ERROR at line 18:
ORA-30353: expression not supported for query rewrite
thanks in advance.
thanks for ur response,
but that link doesn't helps me..,
Try to change query_rewrite_enabled to FALSE and then check. Are u using Oracle 10g version? If so this note says it's a bug Note:359826.1
iam using Oracle9.2
i tried with setting query_rewrite_enables=false
now it shows error as
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Can you post your entire sql. Are the tables being used IOT or normal table.
here is the code:
we are using normal tables
create materialized view snack1
refresh on commit
enable query rewrite
SELECT COUNT(*), MENU_MEAL.person_nr , MENU_MEAL.consume_Date,MENU_MEAL.meal_type_code
FROM MENU_MEAL,PERSON p
AND p.status_code IN('ACTV','CHNG')
AND MENU_MEAL.meal_type_code = 'S1'
AND TRUNC(MENU_MEAL.consume_date) >= TRUNC(SYSDATE)
AND MENU_MEAL. person_nr > 0
AND p.email not like '%test%'
GROUP BY MENU_MEAL.person_nr,consume_date,meal_type_code
HAVING COUNT(*) > 1;
ok, i removed refresh on commit now
it works ..
without query_rewrite_enable true how it will consider optimal query.
As far as I know you can't use a ON COMMIT refersh for a MV containing a GROUP BY
i think you can now set TRUE for query_rewrite_enable and this should work.
Click Here to Expand Forum to Full Width