{"id":6544,"date":"2015-12-17T17:51:05","date_gmt":"2015-12-17T16:51:05","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/"},"modified":"2015-12-17T17:51:05","modified_gmt":"2015-12-17T16:51:05","slug":"ocm-12c-preparation-explain-fast-refresh","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/","title":{"rendered":"OCM 12c preparation: Explain fast refresh"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nThere are some rules to be able to fast refresh a materialized view (which means, refresh it so that it is not stale, and without running the whole query). Documentation is in the Datawarehouse Guide, but we can use Enterprise Manager to get quickly to our goal.
\n<\/p>\n

Let’s see which tables we have:<\/p>\n

\"CaptureMV001\"<\/a><\/p>\n

and create materialized views:<\/p>\n

\"CaptureMV002\"<\/a><\/p>\n

I want to materialize the following join and group by:<\/p>\n

\nselect deptno,dname,count(*),sum(sal) \nfrom scott.dept join scott.emp using (deptno) \ngroup by deptno,dname\n<\/code><\/pre>\n
when entering the query you can run ‘explain’:<\/p>\n
\"CaptureMV003\"<\/a><\/p>\n
here is what is displayed by the explain<\/p>\n
\"CaptureMV004\"<\/a><\/p>\n
For fast refresh we need materialized view log, so let’s create them:<\/p>\n
\"CaptureMV005\"<\/a><\/p>\n
I keep the default: primary key and choose the other column I will use in my materialized view:<\/p>\n
\"CaptureMV006\"<\/a><\/p>\n
I do it for both tables, here is what ‘show sql’ displays:<\/p>\n
\nCREATE MATERIALIZED VIEW LOG ON SCOTT.DEPT NOCACHE WITH PRIMARY KEY (\"DNAME\") EXCLUDING NEW VALUES \nCREATE MATERIALIZED VIEW LOG ON SCOTT.EMP NOCACHE WITH PRIMARY KEY (\"DEPTNO\", \"SAL\") EXCLUDING NEW VALUES \n<\/code><\/pre>\n
Note that there is no comma between the with clause and the column list. If you put one, you can have strange behaviour.<\/p>\n
so here they are:<\/p>\n
\"CaptureMV007\"<\/a><\/p>\n
and let’s explain our mview again:<\/p>\n
\"CaptureMV008\"<\/a><\/p>\n
In order to support fast refresh for all kind of DML, I need to add the following ‘with’ clause:<\/p>\n
\nALTER MATERIALIZED VIEW LOG ON DEPT ADD SEQUENCE, ROWID INCLUDING NEW VALUES;\nALTER MATERIALIZED VIEW LOG ON EMP ADD SEQUENCE, ROWID INCLUDING NEW VALUES;\n<\/code><\/pre>\n
You can do it from the GUI, but I don’t want to navigate though screens again.<\/p>\n
So the result is that my materialized view supports fast refresh. But there was something else:<\/p>\n
\"CaptureMV012\"<\/a><\/p>\n
In order to maintain the SUM() and because the SAL column may be null, we need to keep a count of non null values.<\/p>\n
\nselect deptno,dname,count(*),sum(sal),count(sal)\nfrom scott.dept join scott.emp using (deptno) \ngroup by deptno,dname\n<\/code><\/pre>\n
Now everything is ok:<\/p>\n
\"CaptureMV009\"<\/a><\/p>\n
I can use the ‘get recommendation’ to see that there’s nothing else to do:<\/p>\n
\"CaptureMV010\"<\/a><\/p>\n
Here is the SQL generated:<\/p>\n
\nCREATE MATERIALIZED VIEW \"SCOTT\".\"MV_EMP_DEPT\" USING INDEX REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE AS \nselect deptno,dname,count(*),sum(sal),count(sal) \nfrom scott.dept join scott.emp using (deptno) \ngroup by deptno,dname\nBEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT', tabname => 'MV_EMP_DEPT'); END;\n<\/code><\/pre>\n
dbms_mview<\/h3>\n
If you don’t have Enterprise Manager, you can do the same manually.<\/p>\n
First create the table to store the result:<\/p>\n
\n$ ( cd $ORACLE_HOME\/rdbms\/admin ; ls *xmv*sql; )\nutlxmv.sql\n<\/code><\/pre>\n
(yes I don’t have to remember the name, I just remember ‘xmv’ for explain mview and ‘xrw’ for explain rewrite)<\/p>\n
\n$ sqlplus scott\/tiger @ ?\/rdbms\/admin\/utlxmv.sql\n \nSQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 17 15:09:36 2015\nCopyright (c) 1982, 2011, Oracle.  All rights reserved.\n \nConnected to:\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\n \nTable created.\n \nSQL> l\n  1  CREATE TABLE MV_CAPABILITIES_TABLE\n...\n \n<\/code><\/pre>\n
And here is the result after running<\/p>\n
\nexec dbms_mview.explain_mview('SCOTT.MV_EMP_DEPT');\nselect * from scott.MV_CAPABILITIES_TABLE;\n<\/code><\/pre>\n
on SQL Developer
\n\"CaptureMV013\"<\/a><\/p>\n
That was about refresh. The rewrite capabilities can be explained in a similar way, but that was in a previous blog post<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . There are some rules to be able to fast refresh a materialized view (which means, refresh it so that it is not stale, and without running the whole query). Documentation is in the Datawarehouse Guide, but we can use Enterprise Manager to get quickly to our goal.<\/p>\n","protected":false},"author":27,"featured_media":6557,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[714,96],"type_dbi":[],"class_list":["post-6544","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-ocm-12c","tag-oracle"],"acf":[],"yoast_head":"\nOCM 12c preparation: Explain fast refresh - dbi Blog<\/title>\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\/ocm-12c-preparation-explain-fast-refresh\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"OCM 12c preparation: Explain fast refresh\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . There are some rules to be able to fast refresh a materialized view (which means, refresh it so that it is not stale, and without running the whole query). Documentation is in the Datawarehouse Guide, but we can use Enterprise Manager to get quickly to our goal.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-17T16:51:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"735\" \/>\n\t<meta property=\"og:image:height\" content=\"514\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\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=\"3 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\/ocm-12c-preparation-explain-fast-refresh\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"OCM 12c preparation: Explain fast refresh\",\"datePublished\":\"2015-12-17T16:51:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/\"},\"wordCount\":346,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg\",\"keywords\":[\"OCM 12c\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/\",\"name\":\"OCM 12c preparation: Explain fast refresh - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg\",\"datePublished\":\"2015-12-17T16:51:05+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg\",\"width\":735,\"height\":514},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"OCM 12c preparation: Explain fast refresh\"}]},{\"@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: Explain fast refresh - dbi Blog","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\/ocm-12c-preparation-explain-fast-refresh\/","og_locale":"en_US","og_type":"article","og_title":"OCM 12c preparation: Explain fast refresh","og_description":"By Franck Pachot . There are some rules to be able to fast refresh a materialized view (which means, refresh it so that it is not stale, and without running the whole query). Documentation is in the Datawarehouse Guide, but we can use Enterprise Manager to get quickly to our goal.","og_url":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/","og_site_name":"dbi Blog","article_published_time":"2015-12-17T16:51:05+00:00","og_image":[{"width":735,"height":514,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg","type":"image\/jpeg"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"OCM 12c preparation: Explain fast refresh","datePublished":"2015-12-17T16:51:05+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/"},"wordCount":346,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg","keywords":["OCM 12c","Oracle"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/","url":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/","name":"OCM 12c preparation: Explain fast refresh - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg","datePublished":"2015-12-17T16:51:05+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureMV010-1.jpg","width":735,"height":514},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/ocm-12c-preparation-explain-fast-refresh\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"OCM 12c preparation: Explain fast refresh"}]},{"@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\/6544","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=6544"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6544\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/6557"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6544"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6544"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6544"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6544"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}