12c upgrade<\/a> I checked quickly what I’ve prepared… and it doesn’t work. This post is about query rewrite, a bug in 12c, how to understand why it doesn’t rewrite, and how rewrite is accepted when constraints are not enforced and when materialized view is stale.
\n<\/p>\nSCOTT<\/h1>\n
\nI start with the SCOTT schema where I alter EMP to define DEPTNO as not null, meaning that I’ve a one-to-many association.<\/p>\n
\nSQL> host sqlplus \/ as sysdba @ ?\/rdbms\/admin\/utlsampl\nSQL> alter table SCOTT.EMP modify ( DEPTNO not null);\nTable altered.\n<\/code><\/pre>\nAnd I’ll need a few additional grants for my test.<\/p>\n
\nSQL> grant create materialized view, select any dictionary to scott;\nGrant succeeded.\n<\/code><\/pre>\n<\/p>\nCreate materialized view<\/h1>\n
\nI’m creating a materialized view that join the two tables DEPT and EMP<\/p>\n
\nSQL> connect scott\/tiger\nConnected.\nSQL> \nSQL> create materialized view TEST_MV enable query rewrite\n 2 as\n 3 select * from DEPT join EMP using (DEPTNO);\nMaterialized view created.\n<\/code><\/pre>\nI check that query_rewrite_integrity is enabled<\/p>\n
\nSQL> show parameter rewrite\n \nNAME TYPE VALUE\n------------------------------------ ----------- ------------------------------\nquery_rewrite_enabled string TRUE\nquery_rewrite_integrity string enforced\n<\/code><\/pre>\nand run a query on EMP. The query should be able to read the materialized view instead of the table, because it has all rows and all columns. Because the cost may not be better, I force the query rewrite with the REWRITE_OR_ERROR hint: force the rewrite and fail if not possible.<\/p>\n
\nSQL> set autotrace on explain\nSQL> select \/*+ rewrite_or_error *\/ * from EMP;\nselect \/*+ rewrite_or_error *\/ * from EMP\n *\nERROR at line 1:\nORA-30393: a query block in the statement did not rewrite\n \nSQL> set autotrace off\n<\/code><\/pre>\nHummm. No rewrite. If you try it on 11.2.0.3 query rewrite occurs. But not in 11.2.0.4 not in 12.1\n<\/p>\n
Explain rewrite<\/h1>\n
\nWhen you have to understand why query rewrite do not occur, you can have more information with dbms_mview.explain_rewrite<\/strong>. In 11g we had the possibility to do it easily with dbconsole, but in 12c there is nothing in EM Express nor in SQL Developer. If I’m wrong, please tell me.\n<\/p>\n\nThus, we have to use it in command line. First, we need to create the EXPLAIN_REWRITE table to store the result.<\/p>\n
\nSQL> @ ?\/rdbms\/admin\/utlxrw.sql\n<\/code><\/pre>\n<\/p>\n\nSQL> Rem\nSQL> Rem $Header: rdbms\/admin\/utlxrw.sql \/main\/7 2011\/05\/20 12:21:56 traney Exp $\nSQL> Rem\nSQL> Rem utlxrw.sql\nSQL> Rem\nSQL> Rem Copyright (c) 2000, 2011, Oracle and\/or its affiliates.\nSQL> Rem All rights reserved.\nSQL> Rem\nSQL> Rem NAME\nSQL> Rem utlxrw.sql - Create the output table for EXPLAIN_REWRITE\nSQL> Rem\nSQL> Rem DESCRIPTION\nSQL> Rem Outputs of the EXPLAIN_REWRITE goes into the table created\nSQL> Rem by utlxrw.sql (called REWRITE_TABLE). So utlxrw must be\nSQL> Rem invoked before any EXPLAIN_REWRITE tests.\nSQL> Rem\nSQL> Rem NOTES\nSQL> Rem If user specifies a different name in EXPLAIN_REWRITE, then\nSQL> Rem it should have been already created before calling EXPLAIN_REWRITE.\nSQL> Rem\nSQL> Rem MODIFIED (MM\/DD\/YY)\nSQL> Rem traney\t04\/05\/11 - 35209: long identifiers dictionary upgrade\nSQL> Rem desingh\t10\/19\/05 - bug#4401918:change join_back cols lengths\nSQL> Rem mthiyaga\t04\/29\/05 - Remove unncessary comment\nSQL> Rem mthiyaga\t06\/08\/04 - Add rewritten_txt field\nSQL> Rem mthiyaga\t10\/10\/02 - Add extra columns\nSQL> Rem mthiyaga\t09\/27\/00 - Create EXPLAIN_REWRITE output table\nSQL> Rem mthiyaga\t09\/27\/00 - Created\nSQL> Rem\nSQL> Rem\nSQL> CREATE TABLE REWRITE_TABLE(\n 2 \t\t statement_id\t VARCHAR2(30), -- id for the query\n 3 \t\t mv_owner \t VARCHAR2(128), -- owner of the MV\n 4 \t\t mv_name\t\t VARCHAR2(128), -- name of the MV\n 5 \t\t sequence \t INTEGER,\t -- sequence no of the error msg\n 6 \t\t query\t\t VARCHAR2(4000),-- user query\n 7 \t\t query_block_no\t INTEGER,\t -- block no of the current subquery\n 8 \t\t rewritten_txt\t VARCHAR2(4000),-- rewritten query\n 9 \t\t message\t\t VARCHAR2(512), -- EXPLAIN_REWRITE error msg\n 10 \t\t pass\t\t VARCHAR2(3), -- rewrite pass no\n 11 \t\t mv_in_msg\t VARCHAR2(128), -- MV in current message\n 12 \t\t measure_in_msg\t VARCHAR2(30), -- Measure in current message\n 13 \t\t join_back_tbl\t VARCHAR2(4000),-- Join back table in current msg\n 14 \t\t join_back_col\t VARCHAR2(4000),-- Join back column in current msg\n 15 \t\t original_cost\t INTEGER,\t -- Cost of original query\n 16 \t\t rewritten_cost\t INTEGER,\t -- Cost of rewritten query\n 17 \t\t flags\t\t INTEGER,\t -- associated flags\n 18 \t\t reserved1\t INTEGER,\t -- currently not used\n 19 \t\t reserved2\t VARCHAR2(10)) -- currently not used\n 20 \/\n \nTable created.\n<\/code><\/pre>\nand call the explain_rewrite procedure with the statement, and to materialized view:<\/p>\n
\nSQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');\n \nPL\/SQL procedure successfully completed.\n<\/code><\/pre>\nand check the result:<\/p>\n
\nSQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;\n \n SEQUENCE MESSAGE\n---------- ------------------------------------------------------------\n 1 QSM-01150: query did not rewrite\n 2 QSM-01219: no suitable materialized view found to rewrite th\n is query\n<\/code><\/pre>\nBut there is no reason there. It’s like the materialized view cannot even be considered for query rewrite. Well, I’ll not go into the detail here. I’ve opened bug 17651484 for that. There is a problem since 11.2.0.4 where query uses ANSI join. I like ANSI join syntax, but here I have to avoid them.\n<\/p>\n
Without ANSI join<\/h1>\n
\nSQL> drop materialized view TEST_MV;\nMaterialized view dropped.\n\nSQL> create materialized view TEST_MV enable query rewrite\n 2 as\n 3 select DNAME,EMP.* from DEPT , EMP where DEPT.DEPTNO=EMP.DEPTNO;\n\nMaterialized view created.\n<\/code><\/pre>\nand now check rewrite:<\/p>\n
\nSQL> set autotrace on explain\nSQL> select \/*+ rewrite_or_error *\/ * from EMP;\n \n EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\n 7369 SMITH CLERK 7902 17-DEC-80 800 20\n 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30\n 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30\n 7566 JONES MANAGER 7839 02-APR-81 2975 20\n 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30\n 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30\n 7782 CLARK MANAGER 7839 09-JUN-81 2450 10\n 7788 SCOTT ANALYST 7566 19-APR-87 3000 20\n 7839 KING PRESIDENT 17-NOV-81 5000 10\n 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30\n 7876 ADAMS CLERK 7788 23-MAY-87 1100 20\n 7900 JAMES CLERK 7698 03-DEC-81 950 30\n 7902 FORD ANALYST 7566 03-DEC-81 3000 20\n 7934 MILLER CLERK 7782 23-JAN-82 1300 10\n\n14 rows selected.\n\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 1627509066\n\n----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 14 | 546 | 3 (0)| 00:00:01 |\n| 1 | MAT_VIEW REWRITE ACCESS FULL| TEST_MV | 14 | 546 | 3 (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n\nSQL> set autotrace off\n<\/code><\/pre>\nGood. Query rewrite occured. Here is the output from explain rewrite:<\/p>\n
\nSQL> delete from REWRITE_TABLE;\n2 rows deleted.\n \nSQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');\nPL\/SQL procedure successfully completed.\n\nSQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;\n\n SEQUENCE MESSAGE\n---------- ------------------------------------------------------------\n 1 QSM-01151: query was rewritten\n 2 QSM-01033: query rewritten with materialized view, TEST_MV\n<\/code><\/pre>\nFrom now, I’ll use only explain_rewrite to check different variations.\n<\/p>\n
Disabled constraint<\/h1>\n
\nConstraints are not only there to verify integrity constraints, but also to give information to the optimizer. I’ve added the not null before because using the join instead of the table is possible only when we know that all rows from EMP have a matching row in DEPT. Now let’s see what happens if I remove the constraint:<\/p>\n
\nSQL> alter table EMP disable constraint FK_DEPTNO;\nTable altered.\n\nSQL> delete from REWRITE_TABLE;\n2 rows deleted.\n\nSQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');\nPL\/SQL procedure successfully completed.\n\nSQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;\n\n SEQUENCE MESSAGE\n---------- ------------------------------------------------------------\n 1 QSM-01150: query did not rewrite\n 2 QSM-01110: query rewrite not possible with materialized view\n TEST_MV because it contains a join between tables (EMP and\n DEPT) that is not present in the query and that potentially\n eliminates rows needed by the query\n 3 QSM-01052: referential integrity constraint on table, EMP, n\n ot VALID in ENFORCED integrity mode\n<\/code><\/pre>\nThe explain rewrite tells me everything. The join may eliminate rows, and the disabled constraint cannot ensure that no rows are eliminated.\n<\/p>\n
RELY constraint<\/h1>\n
\nIf you know that your data verifies the constraint, and don’t want the overhead to validate it, you can alter the constraint to RELY so that the optimizer can rely on it.<\/p>\n
\nSQL> alter table EMP modify constraint FK_DEPTNO rely;\nTable altered.\n\nSQL> delete from REWRITE_TABLE;\n3 rows deleted.\n\nSQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');\nPL\/SQL procedure successfully completed.\n\nSQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;\n\n SEQUENCE MESSAGE\n---------- ------------------------------------------------------------\n 1 QSM-01150: query did not rewrite\n 2 QSM-01110: query rewrite not possible with materialized view\n TEST_MV because it contains a join between tables (EMP and\n DEPT) that is not present in the query and that potentially\n eliminates rows needed by the query\n 3 QSM-01052: referential integrity constraint on table, EMP, n\n ot VALID in ENFORCED integrity mode\n<\/code><\/pre>\nBut that’s not enough. We have to read the last message. It’s not sufficient to have the constraint in RELY, we have to be trusted by the query rewrite.<\/p>\n
\nSQL> alter session set query_rewrite_integrity=trusted;\nSession altered.\n\nSQL> delete from REWRITE_TABLE;\n3 rows deleted.\n\nSQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');\nPL\/SQL procedure successfully completed.\n\nSQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;\n\n SEQUENCE MESSAGE\n---------- ------------------------------------------------------------\n 1 QSM-01151: query was rewritten\n 2 QSM-01033: query rewritten with materialized view, TEST_MV\n<\/code><\/pre>\nGood. Now query rewrite occurs because I told the optimizer that it can RELY on the foreign key for the current data, and I told the query rewrite that it can trust me.<\/p>\n
STALE<\/h1>\n
\nOk, but my constraint is still disabled. New DML will not be verified. The RELY concerns only current data. So what happens if I insert a row in EMP that has no matching DEPT?<\/p>\n
\nSQL> insert into EMP (EMPNO,ENAME,DEPTNO) values (9999,'rebel',50);\n1 row created.\n \nSQL> commit;\nCommit complete.\n<\/code><\/pre>\n\nSQL> delete from REWRITE_TABLE;\n2 rows deleted.\n\nSQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');\nPL\/SQL procedure successfully completed.\n\nSQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;\n\n SEQUENCE MESSAGE\n---------- ------------------------------------------------------------\n 1 QSM-01150: query did not rewrite\n 2 QSM-01031: materialized view, TEST_MV, is stale in TRUSTED i\n ntegrity mode\n<\/code><\/pre>\nBecause changes have been done, the materialized view is stale. We need to lower the query rewrite security level if we still want query rewrite:<\/p>\n
\nSQL> alter session set query_rewrite_integrity=stale_tolerated;\nSession altered.\n\nSQL> delete from REWRITE_TABLE;\n2 rows deleted.\n\nSQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');\nPL\/SQL procedure successfully completed.\n\nSQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;\n\n SEQUENCE MESSAGE\n---------- ------------------------------------------------------------\n 1 QSM-01151: query was rewritten\n 2 QSM-01033: query rewritten with materialized view, TEST_MV\n<\/code><\/pre>\nBut then the select with query rewrite will not show all rows, because the row I’ve inserted into EMP is not in the join result.\n<\/p>\n
ENFORCED<\/h1>\n<\/p>\n
So trusted may show wrong results when the constraint is not enabled. You need to enforce query rewrite integrity to avoid that.<\/p>\n
\nSQL> alter session set query_rewrite_integrity=enforced;\nSession altered.\n\nSQL> delete from REWRITE_TABLE;\n2 rows deleted.\n\nSQL> exec dbms_mview.explain_rewrite('select * from EMP','TEST_MV');\nPL\/SQL procedure successfully completed.\n\nSQL> select sequence,message from REWRITE_TABLE order by statement_id,sequence;\n\n SEQUENCE MESSAGE\n---------- ------------------------------------------------------------\n 1 QSM-01150: query did not rewrite\n 2 QSM-01110: query rewrite not possible with materialized view\n TEST_MV because it contains a join between tables (EMP and\n DEPT) that is not present in the query and that potentially\n eliminates rows needed by the query\n 3 QSM-01052: referential integrity constraint on table, EMP, n\n ot VALID in ENFORCED integrity mode\n<\/code><\/pre>\nIn this case, query rewrite do not occur because the foreign key is not enforced by oracle (not enabled).\n<\/p>\n
Conclusion<\/h1>\n
\nQuery rewrite depends on referential integrity. When query_rewrite_integrity=enforced<\/strong>, the referential integrity must be enforced by oracle (constraint is validated and enabled). When query_rewrite_integrity=trusted<\/strong>, you are responsible about data integrity, and control whether rewrite can rely on your constraint, and even tolerate stale materialized view.\n<\/p>\n\nDon’t forget to call dbms_mview.explain_rewrit<\/strong>e after creating the rewrite_table<\/strong> with utlxrw.sql<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"By Franck Pachot . When I passed the OCM 11g exam, I had to prepare the ‘Manage Materialized Views to improve rewrite’ topic. Now that I’m preparing the 12c upgrade I checked quickly what I’ve prepared… and it doesn’t work. This post is about query rewrite, a bug in 12c, how to understand why it […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[368,59],"tags":[604,605,606,607,96,209,608],"type_dbi":[],"class_list":["post-5202","post","type-post","status-publish","format-standard","hentry","category-development-performance","category-oracle","tag-11-2-0-4","tag-hint","tag-materialized-view","tag-ocm","tag-oracle","tag-oracle-12c","tag-rewrite_or_error"],"acf":[],"yoast_head":"\n
OCM 12c preparation: Materialized View explain_rewrite - dbi Blog<\/title>\n<meta name=\"description\" content=\"Materialized view query rewrite.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"OCM 12c preparation: Materialized View explain_rewrite\" \/>\n<meta property=\"og:description\" content=\"Materialized view query rewrite.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-08-23T10:58:51+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"OCM 12c preparation: Materialized View explain_rewrite\",\"datePublished\":\"2015-08-23T10:58:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/\"},\"wordCount\":772,\"commentCount\":0,\"keywords\":[\"11.2.0.4\",\"hint\",\"materialized view\",\"OCM\",\"Oracle\",\"Oracle 12c\",\"rewrite_or_error\"],\"articleSection\":[\"Development & Performance\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/\",\"name\":\"OCM 12c preparation: Materialized View explain_rewrite - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-08-23T10:58:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"Materialized view query rewrite.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"OCM 12c preparation: Materialized View explain_rewrite\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"OCM 12c preparation: Materialized View explain_rewrite - dbi Blog","description":"Materialized view query rewrite.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/","og_locale":"en_US","og_type":"article","og_title":"OCM 12c preparation: Materialized View explain_rewrite","og_description":"Materialized view query rewrite.","og_url":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/","og_site_name":"dbi Blog","article_published_time":"2015-08-23T10:58:51+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"OCM 12c preparation: Materialized View explain_rewrite","datePublished":"2015-08-23T10:58:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/"},"wordCount":772,"commentCount":0,"keywords":["11.2.0.4","hint","materialized view","OCM","Oracle","Oracle 12c","rewrite_or_error"],"articleSection":["Development & Performance","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/","url":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/","name":"OCM 12c preparation: Materialized View explain_rewrite - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-08-23T10:58:51+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"Materialized view query rewrite.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/materialized-view-explain_rewrite\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"OCM 12c preparation: Materialized View explain_rewrite"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5202","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5202"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5202\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5202"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5202"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5202"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5202"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}