Thanks Stefan!
You helped me by slowing me down. I was doing /*+ rewrite_or_error */
but that didn't give me the correct answer. The problem was that I
cannot do rewrite equivalence with query integrity set to enforced.
DBMS_ADVANCED_REWRITE enables me to decide which queries will get
rewritten and by what. I thought that /*+ rewrite_or_error */ will be
sufficient diagnostic, but I was wrong. Explain gave it all away.
Regards
On 9/25/19 12:17 PM, Stefan Knecht wrote:
Have you tried the explain rewrite?
https://blog.dbi-services.com/materialized-view-explain_rewrite/
It should show you exactly why it doesn't get rewritten.
On Wed, Sep 25, 2019 at 11:01 PM Mladen Gogala <gogala.mladen@xxxxxxxxx <mailto:gogala.mladen@xxxxxxxxx>> wrote:
This is 12.2 EE on OL 7.7, with July 2019 PSU applied. User is
running a
report from a view, let's call it VW, and it is horribly slow. The
report is written using Cognos and the developer says it is carved in
stone and cannot be changed before the next millennium, invoking
dirty
language like "release process", "change control","ticket" and "peer
review", which would take forever and a day.
I created a materialized view, let's call it MV_VW, with the
exactly the
same query as the view but when I do something like:
select /*+ rewrite(MV_VW) */ count(*) from VW
it is not getting rewritten. I even tried putting in merge hint, to
merge the view text with the query and then have it all rewritten.
Query
rewrite enabled is in the MV creation and
query_rewrite_enabled=force is
set on the session level. Is there anything I can do, short of
manipulating the baseline?
-- Mladen Gogala
Database Consultant
Tel: (347) 321-1217
--
//www.freelists.org/webpage/oracle-l
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net <http://zztat.net/> | @zztat_oracle | fb.me/zztat <http://fb.me/zztat> | zztat.net/blog/ <http://zztat.net/blog/>
