5 Replies Latest reply on Aug 10, 2020 9:24 AM by Jonathan Lewis

    Query rewrite option is not working

    Ram_A

      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.

        • 1. Re: Query rewrite option is not working
          Saubhik

          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

          • 2. Re: Query rewrite option is not working
            Jonathan Lewis

            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

            • 3. Re: Query rewrite option is not working
              Ram_A

              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.

              • 4. Re: Query rewrite option is not working
                Saubhik

                I can't reproduce your error.

                [saubhik@saubhik ~]$ sql scott/tiger@pdb1
                
                SQLcl: Release 19.1 Production on Mon Aug 10 14:26:17 2020
                
                Copyright (c) 1982, 2020, Oracle.  All rights reserved.
                
                Connected to:
                Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
                Version 19.3.0.0.0
                
                Oracle SQLDeveloper Command-Line (SQLcl) version: 19.1.0.0
                *************************************************
                         My Database Version
                         ====================
                Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
                Version 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_OUTPUT
                Plan 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  >
                
                
                • 5. Re: Query rewrite option is not working
                  Jonathan Lewis

                  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.