Forum Stats

  • 3,733,833 Users
  • 2,246,829 Discussions
  • 7,856,888 Comments

Discussions

Query rewrite option is not working

Ram_A
Ram_A Member Posts: 103 Red Ribbon
edited August 2020 in SQL & PL/SQL

Hi Experts,

Query rewrite option is not working.

I have created the following materialized view  with "enable query rewrite option".

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

create materialized VIEW mv12

REFRESH COMPLETE ON demand

enable query rewrite

as

select d.deptno,sum(e.sal) from emp e,dept d

where e.deptno=d.deptno

GROUP BY d.deptno;

The query is using tables instead of MVIEW. Could you please help me.

Why it'snot using MVIEW.

select d.deptno,sum(e.sal) from emp e,dept d

where e.deptno=d.deptno

and d.deptno=10

GROUP BY d.deptno;

Thanks in advance.

Saubhik

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited August 2020 Accepted Answer

    I assume the reply you made to me was supposed to go to Saubhik - I didn't ask for proof that rewrite wasn't working, I suggested you use dbms_mview.explain_mview to check that rewrite ought to work, and dbms_mview.explain_rewrite to check why it didn't rewrite.

    Saubhik has now demonstrated that rewrite works for him, and I have to say that I couldn't see any reason why it shouldn't rewrite.

    If you want to stick with explain plan for the moment,

    a) include the 'outline' format option in case that shows us a hint or optimizer environment setting that explains the oddity

    b) add the hint /*+ rewrite */ to see if the rewrite takes place and what it costs - rewrite is cost-based, and though the "obvious" plan for using the materialized view clearly ought to be lower than the cost of accessing the tables, maybe in your case it isn't.

    Regards

    Jonathan Lewis

    UPDATE - the simplest explanation is that you've updated either the emp or dept table since creating the materialized view and haven't allowed for use of stale MVs in rewrite.

    Saubhik

Answers

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited August 2020

    Hmm, How do you know that it is not using query rewrite? That is not shown in your post, please show that. Have you actually went through your earlier abandoned thread and the documentation link with the explanation provided by mathguy? You could have ask more about the "query rewrite" concept from him in that thread itself.

    Query rewrite option in MVIEW

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited August 2020

    If you had followed the documentation link given by Saubhik in the previous post and done a little follow-up reading you would have found that the package dbms_mview has a procedure explain_rewrite that you can you to explain why the optimizer has not done the rewrite. The package also has a procedure (demonstrated here) called explain_mview that tells you what benefits of materialized views are available to that particular view, and why some features won't work for it.

    Regards

    Jonathan Lewis

  • Ram_A
    Ram_A Member Posts: 103 Red Ribbon
    edited August 2020

    Hi,

    I have generated explain plan there it's not showing MVIEW.

    ----------------------------------------------------------

    | Id  | Operation                    | Name | Cost (%CPU)|

    ----------------------------------------------------------

    |   0 | SELECT STATEMENT             |      |     2   (0)|

    |   1 |  SORT GROUP BY NOSORT        |      |     2   (0)|

    |   2 |   TABLE ACCESS BY INDEX ROWID| EMP  |     2   (0)|

    |*  3 |    INDEX RANGE SCAN          | IDX5 |     1   (0)|

    ----------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       3 - access("E"."DEPTNO"=10)

    Thanks.

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited August 2020

    I can't reproduce your error.

    pastedImage_2.png

    [[email protected] ~]$ sql scott/[email protected]: Release 19.1 Production on Mon Aug 10 14:26:17 2020Copyright (c) 1982, 2020, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0Oracle SQLDeveloper Command-Line (SQLcl) version: 19.1.0.0*************************************************         My Database Version         ====================Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0*************************************************        User: SCOTT        SID: 31        Serial No.: 42130        Session Id: 840116*************************************************I am SCOTT on pdb1  >CREATE MATERIALIZED VIEW mv12  2      REFRESH  3          COMPLETE  4          ON DEMAND  5  ENABLE QUERY REWRITE AS  6      SELECT  7          d.deptno,  8          SUM(e.sal)  9      FROM 10          emp    e, 11          dept   d 12      WHERE 13          e.deptno = d.deptno 14      GROUP BY 15          d.deptno;Materialized view created.I am SCOTT on pdb1  >EXPLAIN PLAN  2      FOR  3  SELECT  4      d.deptno,  5      SUM(e.sal)  6  FROM  7      emp    e,  8      dept   d  9  WHERE 10      e.deptno = d.deptno 11      AND d.deptno = 10 12  GROUP BY 13      d.deptno;Explained.I am SCOTT on pdb1  >select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUTPlan hash value: 46853783-------------------------------------------------------------------------------------| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |      |     1 |     7 |     3   (0)| 00:00:01 ||*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV12 |     1 |     7 |     3   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("MV12"."DEPTNO"=10)13 rows selected.I am SCOTT on pdb1  >
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited August 2020 Accepted Answer

    I assume the reply you made to me was supposed to go to Saubhik - I didn't ask for proof that rewrite wasn't working, I suggested you use dbms_mview.explain_mview to check that rewrite ought to work, and dbms_mview.explain_rewrite to check why it didn't rewrite.

    Saubhik has now demonstrated that rewrite works for him, and I have to say that I couldn't see any reason why it shouldn't rewrite.

    If you want to stick with explain plan for the moment,

    a) include the 'outline' format option in case that shows us a hint or optimizer environment setting that explains the oddity

    b) add the hint /*+ rewrite */ to see if the rewrite takes place and what it costs - rewrite is cost-based, and though the "obvious" plan for using the materialized view clearly ought to be lower than the cost of accessing the tables, maybe in your case it isn't.

    Regards

    Jonathan Lewis

    UPDATE - the simplest explanation is that you've updated either the emp or dept table since creating the materialized view and haven't allowed for use of stale MVs in rewrite.

    Saubhik
Sign In or Register to comment.