Problems with query rewrite
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 25 Sep 2019 11:59:19 -0400
Message-ID: <bab934c6-d955-da08-2312-e52c4fb618a5_at_gmail.com>
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.
Date: Wed, 25 Sep 2019 11:59:19 -0400
Message-ID: <bab934c6-d955-da08-2312-e52c4fb618a5_at_gmail.com>
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
--