SQL> alter session set query_rewrite_enabled=true;
create materialized view csci317.mv_query3_part
ENABLE QUERY REWRITE
as
SELECT count(*),SUM(L_QUANTITY),avg(l_quantity)
FROM csci317.LINEITEM lm, csci317.PARTSUPP pp
WHERE lm.L_PARTKEY = pp.ps_partkey
AND l_shipdate <= to_date ('1998-12-01')
having (count(*) > 10 );Session altered.SQL> 2 3 4 5 6 7 8 having (count(*) > 10 )
*
ERROR at line 8:
ORA-30353: expression not supported for query rewrite
create materialized view csci317.mv_query3_part
ENABLE QUERY REWRITE
as
SELECT count(*),SUM(L_QUANTITY),avg(l_quantity)
FROM csci317.LINEITEM lm, csci317.PARTSUPP pp
WHERE lm.L_PARTKEY = pp.ps_partkey
AND l_shipdate <= to_date ('1998-12-01')
having (count(*) > 10 );Session altered.SQL> 2 3 4 5 6 7 8 having (count(*) > 10 )
*
ERROR at line 8:
ORA-30353: expression not supported for query rewrite
Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE, CURRENT_TIMESTAMP, MAXVALUE, a sequence number, a bind variable, correlation variable, a set result,a trigger return variable, a parallel table queue column, collection iterator, etc.
Action: Remove the offending expression or disable the REWRITE option on the materialized view.