Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
Query rewrite in Oracle 12c
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
Answers
-
Please can you post a simplified example of a query, and the materialized view you are creating?
-
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