Sorry about the delay in replying.
Attached is a simple SQL example of materialized views
which may give you a clue to where things are going
wrong.
Since Oracle can rewrite even complex variations,
a little thing like changing the case is NOT going to
be the problem.
The script builds a demo table of about 3,300 rows
on my system, on a 4K block size for a total of
about 30 blocks. Oracle 8.1.7.0 on NT 4.0
The sample queries all use the materialized view
instead of the base table.
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 30 August 2001 17:21
I've gone thru the FM for about 4 hours now, to no avail.
I thought oracle uppercased tables and columns that were not enclosed
in double quotes("), but for query rewrite to pick up on a
materialized view, the SQL has to be EXACT(oracle supposedly strips
whitespace), but if i spell the table Ft_SALES and the materialized
view sql says FT_SALES, no rewrite is done.
Is this the way its supposed to work, i'd thought oracle would have
been smart enough to do the upper on anythin not in " and then do the
compare,
guess i'm asking for too much.
I'm done venting.
joe
c_mv1.sql
