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 - 14:56:39 CDT
