query rewrite

From: elain he <elainhe_at_hotmail.com>
Date: Wed, 05 Dec 2001 14:18:28 -0800
Message-ID: <F001.003D61FE.20011205135020@fatcity.com>

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: elainhe_at_hotmail.com

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: ListGuru_at_fatcity.com (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). Received on Wed Dec 05 2001 - 16:18:28 CST