Hi, Does anyone know how can I create a materialized view for query rewrite on the following query:
select distinct parttable from emp where exists (select * from addr where ((addr.zip=24811) and emp.timestamp='11/23/2001') and emp.id=addr.id and emp.sn=addr.sn) order by emp.parttable; I tried creating a materialized view but it errored out on the emp.timestamp='11/23/2001'. ORA-30353: expression not supported for query rewrite I created another materialized view without the 'timestamp' and it got created fine. ie select distinct parttable from emp where exists (select * from addr where ((addr.zip=24811)) and emp.id=addr.id and emp.sn=addr.sn) order by emp.parttable; Now, when I try running the query below, Oracle does not do a query rewrite but when I ran the query without referencing 'timestamp', Oracle uses the materialized view. The query below needs to reference the timestamp. How can I create the materialized view to enable Oracle to utilize the materialized view whenever the below query is ran? select distinct parttable from emp where exists (select * from addr where ((addr.zip=24811) and emp.timestamp='11/23/2001') and emp.id=addr.id and emp.sn=addr.sn) order by emp.parttable; thanks in advance for any assistance you can provide. elain _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: elain he INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
