Re: query rewrite question

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 <ORACLE-L_at_fatcity.com> 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

Received on Wed Sep 05 2001 - 05:41:29 CDT