Re: Event to show Query Rewrite

Thanks for the link. The docs specify how to check if query rewrite occured and if so on _which_ MV, not the _exact_ rewritten query. I already deduced from the dbms_mview.explain_rewrite and 10046 trace that the MV_base_table is being selected; what I wanted to find out was what was the query on that MV - was it "select sum(totrows) from MV_base_table" or "select count(*) from MV_base_table". I suspect the latter but can't prove it without a trace.
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: <A [EMAIL PROTECTED] href="mailto:[EMAIL PROTECTED]">Seefelt, Beth To: <A [EMAIL PROTECTED] href="mailto:[EMAIL PROTECTED]">Multiple recipients of list ORACLE-L

  Sent: Tuesday, June 24, 2003 5:19
PM
  Subject: RE: Event to show Query
  Rewrite   

  Its not an event,
  but maybe this will help.
  <FONT face=Arial
  size=2> 
  <A
  href="http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/qr.htm#39292">http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/qr.htm#39292   <FONT face=Arial
  size=2> 
  <FONT face=Arial
  size=2>    

  <FONT
  face=Tahoma size=2>-----Original Message-----From: Arup Nanda   [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 4:05   PMTo: Multiple recipients of list ORACLE-LSubject: Event   to show Query Rewrite
  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 Received on Tue Jun 24 2003 - 17:13:26 CDT