Re: Problems with query rewrite

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_at_gmail.com <mailto: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 <http://zztat.net/> | _at_zztat_oracle |
> fb.me/zztat <http://fb.me/zztat> | zztat.net/blog/
> <http://zztat.net/blog/>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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