Re: Problems with query rewrite

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_at_gmail.com> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l