Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Event to show Query Rewrite

Re: Event to show Query Rewrite

From: Arup Nanda <>
Date: Tue, 24 Jun 2003 15:13:26 -0700
Message-ID: <>

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.
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

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

  Its not an event,
  but maybe this will help.
  <FONT face=Arial
  href="">   <FONT face=Arial
  <FONT face=Arial

  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

  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 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

Original text of this message