{"id":17690,"date":"2024-03-15T15:30:38","date_gmt":"2024-03-15T15:30:38","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/"},"modified":"2024-03-21T11:30:30","modified_gmt":"2024-03-21T11:30:30","slug":"how-can-a-materialized-view-be-created-in-pl-sql","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/","title":{"rendered":"How can a materialized view be created in PL\/SQL?"},"content":{"rendered":"

To create a materialized view in PL\/SQL, you can follow these steps:<\/p>\n

    \n
  1. You can create a new Package or Procedure in PL\/SQL to define a materialized view. You can use the CREATE PACKAGE or CREATE PROCEDURE statements to create it.<\/li>\n
  2. Use the CREATE MATERIALIZED VIEW statement in a Package or Procedure to define the structure and query of a materialized view. The syntax is as follows:<\/li>\n<\/ol>\n
    CREATE MATERIALIZED VIEW mv_name\r\n   [TABLESPACE tablespace_name]\r\n   [CACHE | NOCACHE]\r\n   [PARALLEL parallel_clause]\r\n   [BUILD {IMMEDIATE | DEFERRED}]\r\n   [REFRESH {COMPLETE|FAST|FORCE} [START WITH date] [NEXT date]\r\n      [WITH {PRIMARY KEY|ROWID}]]\r\n   AS select_statement;\r\n<\/code><\/pre>\n
    mv_name is the name of the materialized view, tablespace_name is the name of the tablespace where the materialized view is located, CACHE or NOCACHE is used to specify whether to cache the materialized view in memory, parallel_clause is used to specify the level of parallel query, BUILD is used to specify the construction method of the materialized view (immediate or deferred), REFRESH is used to specify the refresh method of the materialized view, select_statement is the query statement of the materialized view.<\/p>\n
      \n
    1. Use the EXECUTE procedure to run the CREATE MATERIALIZED VIEW statement in a Package or Procedure to create a materialized view.<\/li>\n<\/ol>\n
      For example, here is a sample of creating a materialized view using PL\/SQL:<\/p>\n
      CREATE OR REPLACE PACKAGE my_package AS\r\n   PROCEDURE create_materialized_view;\r\nEND;\r\n\r\nCREATE OR REPLACE PACKAGE BODY my_package AS\r\n   PROCEDURE create_materialized_view AS\r\n   BEGIN\r\n      EXECUTE IMMEDIATE '\r\n         CREATE MATERIALIZED VIEW my_materialized_view\r\n            TABLESPACE my_tablespace\r\n            CACHE\r\n            AS SELECT * FROM my_table';\r\n   END;\r\nEND;\r\n<\/code><\/pre>\n
      Simply execute the create_materialized_view procedure to create the materialized view.<\/p>\n","protected":false},"excerpt":{"rendered":"
      To create a materialized view in PL\/SQL, you can follow these steps: You can create a new Package or Procedure in PL\/SQL to define a materialized view. You can use the CREATE PACKAGE or CREATE PROCEDURE statements to create it. Use the CREATE MATERIALIZED VIEW statement in a Package or Procedure to define the structure […]<\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-17690","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nHow can a materialized view be created in PL\/SQL? - Blog - Silicon Cloud<\/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.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can a materialized view be created in PL\/SQL?\" \/>\n<meta property=\"og:description\" content=\"To create a materialized view in PL\/SQL, you can follow these steps: You can create a new Package or Procedure in PL\/SQL to define a materialized view. You can use the CREATE PACKAGE or CREATE PROCEDURE statements to create it. Use the CREATE MATERIALIZED VIEW statement in a Package or Procedure to define the structure […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-15T15:30:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T11:30:30+00:00\" \/>\n<meta name=\"author\" content=\"Isabella Edwards\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Isabella Edwards\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/\"},\"author\":{\"name\":\"Isabella Edwards\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\"},\"headline\":\"How can a materialized view be created in PL\/SQL?\",\"datePublished\":\"2024-03-15T15:30:38+00:00\",\"dateModified\":\"2024-03-21T11:30:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/\"},\"wordCount\":211,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/\",\"name\":\"How can a materialized view be created in PL\/SQL? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T15:30:38+00:00\",\"dateModified\":\"2024-03-21T11:30:30+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can a materialized view be created in PL\/SQL?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd\",\"name\":\"Isabella Edwards\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g\",\"caption\":\"Isabella Edwards\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can a materialized view be created in PL\/SQL? - Blog - Silicon Cloud","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.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/","og_locale":"en_US","og_type":"article","og_title":"How can a materialized view be created in PL\/SQL?","og_description":"To create a materialized view in PL\/SQL, you can follow these steps: You can create a new Package or Procedure in PL\/SQL to define a materialized view. You can use the CREATE PACKAGE or CREATE PROCEDURE statements to create it. Use the CREATE MATERIALIZED VIEW statement in a Package or Procedure to define the structure […]","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T15:30:38+00:00","article_modified_time":"2024-03-21T11:30:30+00:00","author":"Isabella Edwards","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Isabella Edwards","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/"},"author":{"name":"Isabella Edwards","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd"},"headline":"How can a materialized view be created in PL\/SQL?","datePublished":"2024-03-15T15:30:38+00:00","dateModified":"2024-03-21T11:30:30+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/"},"wordCount":211,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/","name":"How can a materialized view be created in PL\/SQL? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T15:30:38+00:00","dateModified":"2024-03-21T11:30:30+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-a-materialized-view-be-created-in-pl-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can a materialized view be created in PL\/SQL?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/5579144e23c225c8188167f3e3f888dd","name":"Isabella Edwards","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d4d4dec47f553ac7961d9fa4cc9bdcdcf5b7ce5106594330b6d25c5694fdbaec?s=96&d=mm&r=g","caption":"Isabella Edwards"},"url":"https:\/\/www.silicloud.com\/blog\/author\/isabellaedwards\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=17690"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17690\/revisions"}],"predecessor-version":[{"id":51318,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/17690\/revisions\/51318"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=17690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=17690"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=17690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}