Query rewrite in Oracle 12c

Rudi

Hi

We have an issue regarding Oracle 12.1.0.2 query rewrite.

All the queries are generated by OBIEE 12c which heavily uses WITH Clauses  a.k.a Subquery Factoring.

However, we see that even if we create MV which are full sql text match,the query rewrite doesn't happen.

We are using dbms_mview.explain_rewrite,in order to understand the root cause, but we get nowhere.

Any ideas/experience?

Thanks,

Rudi

John_K

Please can you post a simplified example of a query, and the materialized view you are creating?

n12jim

I am also having the same issue.  I tried having the definition of the materialized view be exactly what the BI query was and that did not work.  The Explain Rewrite performance in the Grid Control returns this as to why it is not using the mv:

QSM-01150: query did not rewrite
QSM-01091: cost based optimizer found query rewrite is more expensive

If I run the query outside of BI and put in a hint to use the mv, the results return very fast so I do not understand why the optimizer thinks its less costly to not use the mv.

The BI query is this:

WITH SAWITH0 AS

        (

                SELECT  SUM(T267358.MAT_QUANTITY)         AS c1 ,

                        SUM(T267358.LABOR_HOURS)          AS c2 ,

                        SUM(T267358.EXPENSE_AMT)          AS c3 ,

                        T267358.PO_LINE_NUMBER            AS c4 ,

                        T267358.PO_NUMBER                 AS c5 ,

                        T267358.INV_DESCRIPTION           AS c6 ,

                        T267358.INV_NUMBER                AS c7 ,

                        T267358.EXPENDITURE_TYPE          AS c8 ,

                        T267358.EMP_JOB_CODE              AS c9 ,

                        T267358.ACTUALHIST_ID             AS c10,

                        T267292.PAY_PERIOD_END            AS c11,

                        T267324.BUR                       AS c12,

                        T267324.DBF                       AS c13,

                        T267335.EMP_NAME                  AS c14,

                        T267335.GHRS_EMPL_ID              AS c15,

                        T270971.ACCOUNT_DESC              AS c16,

                        T270971.ACCOUNT                   AS c17,

                        T270716.ACCOUNTING_UNIT_DESC      AS c18,

                        T270716.ACCOUNTING_UNIT           AS c19,

                        T270716.CLASS_CODE                AS c20,

                        T270716.CLASS_DESC                AS c21,

                        T270716.COMPANY_DESC              AS c22,

                        T270716.COMPANY                   AS c23,

                        T270971.SUB_ACCOUNT_DESC          AS c24,

                        T270971.SUB_ACCOUNT               AS c25,

                        T267292.DATE_DESC                 AS c26,

                        T267292.STATEFY_YR_NUM            AS c27,

                        T265893.PHASE_DESC                AS c28,

                        T265814.PRJ_NAME_TOWN             AS c29,

                        T265814.STATE_PRJ_NUM_SUFFIX_CONT AS c30,

                        T268260.LOCATION_CODE             AS c31,

                        T268260.VENDOR_CODE               AS c32,

                        T268260.VENDOR_NAME               AS c33,

                        T265893.ACCOMP_UOM_ID             AS c34,

                        T269952.ACTIVITY                  AS c35,

                        T265893.MATS_WCC                  AS c36,

                        T265893.WC_LONG_DESC              AS c37,

                        T269952.PROGRAM_TITLE             AS c38,

                        T265893.PHASE_NUM                 AS c39,

                        T267292.DATETIME_ID               AS c40,

                        T267324.DBF_ID                    AS c41,

                        T265893.PHASE_ID                  AS c42

                FROM    DOTDW.D_FUNDING_ACCOUNT T270971

                        /* D7.6 Funding Account */

                        ,

                        DOTDW.D_FUNDING_STRING T270716

                        /* D7.5 Funding String */

                        ,

                        DOTDW.D_WCC_PROGRAM_ACTIVITY T269952

                        /* D9.2 WC Programs Activities */

                        ,

                        DOTDW.D_WORKCLASS T265893

                        /* D5 Work Class */

                        ,

                        DOTDW.D_VENDORS T268260

                        /* D11 Vendors */

                        ,

                        DOTDW.D_DIST_BUR_FOREMAN T267324

                        /* D8 District Foreman */

                        ,

                        DOTDW.D_EMPLOYEES T267335

                        /* D7 Employees */

                        ,

                        DOTDW.D_PROJECTS T265814

                        /* D4 Project */

                        ,

                        DOTDW.D_DATETIME T267292

                        /* D1 Manifest Date Expense */

                        ,

                        DOTDW.F_EXPENDITURE T267358

                        /* F2 Expenditures */

                WHERE

                        (

                                T265893.WCC_PROG_ACT_ID        = T269952.WCC_PROG_ACT_ID

                                AND T265893.WORKCLASS_ID       = T267358.WORKCLASS_ID

                                AND T267358.VENDOR_ID          = T268260.VENDORID

                                AND T267324.DBF_ID             = T267358.DBF_ID

                                AND T267335.EMPLOYEE_ID        = T267358.EMPLOYEE_ID

                                AND T265814.PROJECT_ID         = T267358.PROJECT_ID

                                AND T267292.DATETIME_ID        = T267358.MANIFEST_DATE_ID

                                AND T267292.STATEFY_YR_NUM     = 2018

                                AND T267358.FUND_STRING_ID     = T270716.FUND_STRING_ID

                                AND T267358.FUNDING_ACCOUNT_ID = T270971.FUNDING_ACCOUNT_ID

                        )

                GROUP BY T265814.PRJ_NAME_TOWN           ,

                        T265814.STATE_PRJ_NUM_SUFFIX_CONT,

                        T265893.ACCOMP_UOM_ID            ,

                        T265893.MATS_WCC                 ,

                        T265893.WC_LONG_DESC             ,

                        T265893.PHASE_ID                 ,

                        T265893.PHASE_NUM                ,

                        T265893.PHASE_DESC               ,

                        T267292.DATETIME_ID              ,

                        T267292.DATE_DESC                ,

                        T267292.STATEFY_YR_NUM           ,

                        T267292.PAY_PERIOD_END           ,

                        T267324.BUR                      ,

                        T267324.DBF                      ,

                        T267324.DBF_ID                   ,

                        T267335.EMP_NAME                 ,

                        T267335.GHRS_EMPL_ID             ,

                        T267358.ACTUALHIST_ID            ,

                        T267358.EXPENDITURE_TYPE         ,

                        T267358.INV_DESCRIPTION          ,

                        T267358.INV_NUMBER               ,

                        T267358.EMP_JOB_CODE             ,

                        T267358.PO_LINE_NUMBER           ,

                        T267358.PO_NUMBER                ,

                        T268260.LOCATION_CODE            ,

                        T268260.VENDOR_CODE              ,

                        T268260.VENDOR_NAME              ,

                        T269952.ACTIVITY                 ,

                        T269952.PROGRAM_TITLE            ,

                        T270716.COMPANY_DESC             ,

                        T270716.ACCOUNTING_UNIT          ,

                        T270716.ACCOUNTING_UNIT_DESC     ,

                        T270716.CLASS_CODE               ,

                        T270716.CLASS_DESC               ,

                        T270716.COMPANY                  ,

                        T270971.SUB_ACCOUNT              ,

                        T270971.SUB_ACCOUNT_DESC         ,

                        T270971.ACCOUNT                  ,

                        T270971.ACCOUNT_DESC

        )

SELECT  0      AS c1 ,

        D1.c11 AS c2 ,

        D1.c12 AS c3 ,

        D1.c13 AS c4 ,

        D1.c14 AS c5 ,

        D1.c15 AS c6 ,

        D1.c16 AS c7 ,

        D1.c17 AS c8 ,

        D1.c18 AS c9 ,

        D1.c19 AS c10,

        D1.c20 AS c11,

        D1.c21 AS c12,

        D1.c22 AS c13,

        D1.c23 AS c14,

        D1.c24 AS c15,

        D1.c25 AS c16,

        D1.c26 AS c17,

        D1.c27 AS c18,

        D1.c10 AS c19,

        D1.c9  AS c20,

        D1.c8  AS c21,

        D1.c7  AS c22,

        D1.c6  AS c23,

        D1.c5  AS c24,

        D1.c4  AS c25,

        D1.c28 AS c26,

        D1.c29 AS c27,

        D1.c30 AS c28,

        D1.c31 AS c29,

        D1.c32 AS c30,

        D1.c33 AS c31,

        D1.c34 AS c32,

        D1.c35 AS c33,

        D1.c36 AS c34,

        D1.c37 AS c35,

        D1.c38 AS c36,

        D1.c39 AS c37,

        D1.c3  AS c38,

        D1.c2  AS c39,

        D1.c1  AS c40,

        0      AS c41,

        0      AS c42,

        0      AS c43,

        D1.c40 AS c44,

        D1.c41 AS c45,

        D1.c42 AS c46

FROM    SAWITH0 D1

ORDER BY c17,

        c18 ,

        c28 ,

        c34 ,

        c6  ,

        c3  ,

        c4  ,

        c30 ,

        c26 ,

        c21 ,

        c19 ,

        c10 ,

        c11 ,

        c14 ,

        c8  ,

        c16 ,

        c44 ,

        c27 ,

        c32 ,

        c46 ,

        c35 ,

        c5  ,

        c45 ,

        c29 ,

        c31 ,

        c37 ,

        c23 ,

        c22 ,

        c2  ,

        c33 ,

        c36 ,

        c24 ,

        c25 ,

        c20 ,

        c9  ,

        c12 ,

        c13 ,

        c7  ,

        c15

Thank You,

Joyce