Try with TIMESTAMP literal. Like this: create materialized view mv_test enable query rewrite as select * from birthdays where birth= timestamp '1971-05-03 09:00:00 PM' at time zone 'Europe/London';
Regards. --- elain he <[EMAIL PROTECTED]> wrote: > 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). ===== ENG. Christian Trassens Senior DBA [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +34-699240979 +34-649824704 __________________________________________________ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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).
