Re: Event to show Query Rewrite

2 things, one is that our (on 8i, then updgraded to 9.2.0.1) MVs on prebuilt also show a staleness status of unknown, yet work find. Ok with me we just look for that now.
The other thing is depending on what you're wanting to accomplish, you can simply turn off query rewrite for your session, then the select count will (should) run against the correct table. alter session set query_rewrite_enabled=false;

>>> [EMAIL PROTECTED] 06/24/03 03:04PM >>>
Listers,

Does anyone know how to set an event, or some other method, to see what query Oracle rewrites for a Materialized View and how it arrives at that decision? I am seeing a problem here in my DW database where the query "select count(*) from base_table" is rewritten as "select count(*) from MV_on_the _base_table", a clearly wrong rewrite of the query.

Oracle 9.2.0.3 on Tru64 Unix.

The base table is (col1 date, col2 varchar2(20), col3, col4, col5...) range partitioned on col1 and list subpartitioned on col2. the MV is "select col1, col2, count(*) totrows from base group by col1, col2", also partitioned in the same manner as the base table. The MV is first as a table and then the MV is built with "on prebuilt table" clause.

Query_rewrite_enabled = true and query_rewrite_integrity = stale_tolerated.

A query "select count(*) from base" is probably rewritten as "select count(*) from MV_base", and of course, the results are wrong. Oracle Support asks to provide a test case and I am unable to reproduce it. Upon examination, the STALENESS column in user_mviews is "UNKNOWN" for this MV; STALE for all others. Oracle Support can't explain what could be the cause of this status except that "it happens when an MV is created on a prebuilt table".

My only recourse at this time is to identify the decision making process Oracle used to arrive at the query it rewrites to and perhaps explain that somehow. Any help will be greatly appreciated.

Thanks a lot in advance.

Arup Nanda

                NOTICE BY ZALE CORPORATION

This message, as well as any attached document, contains information from Zale Corporation that is proprietary, confidential and/or privileged. The information is intended only for the use of the addressee named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or the taking of any action in reliance on the contents of this message or its attachments is strictly prohibited, and may be unlawful. If you have received this message in error, please delete all electronic copies of this message and its attachments, if any, destroy any hard copies you may have created, without disclosing the contents, and notify the sender immediately. Unintended transmission does not constitute waiver of any privilege.

Unless expressly stated otherwise, nothing contained in this message should be construed as a digital or electronic signature, nor is it intended to reflect an intention to make an agreement by electronic means.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).