yexinru/doc-exports
1
0
Fork 0
forked from docs/doc-exports
Code Pull Requests Activity
Files
sgode-patch-2
doc-exports/docs/dws/dev/dws_06_0358.html
luhuayi 177cd61a57 DWS DEVG 910.211 version 
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com>
Co-authored-by: luhuayi <luhuayi@huawei.com>
Co-committed-by: luhuayi <luhuayi@huawei.com>
2025-05-05 07:44:03 +00:00

69 lines
14 KiB
HTML
Raw Permalink Blame History

<a name="EN-US_TOPIC_0000001764675134"></a><a name="EN-US_TOPIC_0000001764675134"></a>
<h1 class="topictitle1">ALTER MATERIALIZED VIEW</h1>
<div id="body0000001544753762"><div class="section" id="EN-US_TOPIC_0000001764675134__section595413393479"><h4 class="sectiontitle">Function</h4><p id="EN-US_TOPIC_0000001764675134__p19655175244714">Modifies the properties of a materialized view.</p>
</div>
<div class="section" id="EN-US_TOPIC_0000001764675134__section20626950154720"><h4 class="sectiontitle">Precautions</h4><p id="EN-US_TOPIC_0000001764675134__p22551138155611">None</p>
</div>
<div class="section" id="EN-US_TOPIC_0000001764675134__section4402748164714"><h4 class="sectiontitle">Syntax</h4><div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764675134__screen657523624810"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span>
<span class="normal">4</span>
<span class="normal">5</span>
<span class="normal">6</span>
<span class="normal">7</span>
<span class="normal">8</span>
<span class="normal">9</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">IF</span><span class="w"> </span><span class="k">EXISTS</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="err">{</span><span class="w"> </span><span class="n">materialized_view_name</span><span class="w"> </span><span class="err">}</span>
<span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="n">ENABLE</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="n">DISABLE</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="n">QUERY</span><span class="w"> </span><span class="n">REWRITE</span><span class="p">;</span>
<span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">IF</span><span class="w"> </span><span class="k">EXISTS</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="err">{</span><span class="w"> </span><span class="n">materialized_view_name</span><span class="w"> </span><span class="err">}</span>
<span class="w"> </span><span class="n">REFRESH</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="n">COMPLETE</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="n">DEMAND</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">START</span><span class="w"> </span><span class="k">WITH</span><span class="w"> </span><span class="p">(</span><span class="n">timestamptz</span><span class="p">)</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="p">[</span><span class="w"> </span><span class="k">EVERY</span><span class="w"> </span><span class="p">(</span><span class="nb">interval</span><span class="p">)</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="p">];</span>
<span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="err">{</span><span class="w"> </span><span class="n">materialized_view_name</span><span class="w"> </span><span class="err">}</span>
<span class="w"> </span><span class="k">OWNER</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="n">new_owner</span><span class="p">;</span>
<span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="err">{</span><span class="w"> </span><span class="n">materialized_view_name</span><span class="w"> </span><span class="err">}</span>
<span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="err">{</span><span class="n">storage_parameter</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="n">value</span><span class="err">}</span><span class="w"> </span><span class="p">[,</span><span class="w"> </span><span class="p">...</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="p">)</span>
<span class="w"> </span><span class="o">|</span><span class="w"> </span><span class="k">RESET</span><span class="w"> </span><span class="p">(</span><span class="w"> </span><span class="n">storage_parameter</span><span class="w"> </span><span class="p">[,</span><span class="w"> </span><span class="p">...</span><span class="w"> </span><span class="p">]</span><span class="w"> </span><span class="p">)</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001764675134__section118791645124714"><h4 class="sectiontitle">Parameter Description</h4><ul id="EN-US_TOPIC_0000001764675134__ul1681365573213"><li id="EN-US_TOPIC_0000001764675134__li1746117256335"><strong id="EN-US_TOPIC_0000001764675134__b1432334616330">ENABLE | DISABLE QUERY REWRITE</strong><p id="EN-US_TOPIC_0000001764675134__p6430153893310">Indicates whether to enable query rewriting for a materialized view.</p>
<p id="EN-US_TOPIC_0000001764675134__p1178144675810">After enabling query rewriting for the materialized view, refresh the materialized view to ensure the data is up-to-date.</p>
</li><li id="EN-US_TOPIC_0000001764675134__li19813115533219"><strong id="EN-US_TOPIC_0000001764675134__b2024404963319">REFRESH [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [EVERY (interval)] ]</strong><p id="EN-US_TOPIC_0000001764675134__p1897816116330">Modifies the method of refreshing a materialized view.</p>
<ul id="EN-US_TOPIC_0000001764675134__ul1185931191214"><li id="EN-US_TOPIC_0000001764675134__li1671561071219">Currently, only the <strong id="EN-US_TOPIC_0000001764675134__b1958316514113">COMPLETE</strong> refresh mode is supported, which refresh full data in the materialized view. Execute the query statement defined in the materialized view to update the materialized view.</li><li id="EN-US_TOPIC_0000001764675134__li13300421183">Refresh the triggering method.<p id="EN-US_TOPIC_0000001764675134__p209101551384"><a name="EN-US_TOPIC_0000001764675134__li13300421183"></a><a name="li13300421183"></a><strong id="EN-US_TOPIC_0000001764675134__b15407014541213">ON DEMAND</strong>: manual refresh on demand.</p>
<p id="EN-US_TOPIC_0000001764675134__p117751643171118"><strong id="EN-US_TOPIC_0000001764675134__b89205376041213">START WITH (timestamptz) | EVERY (interval)</strong>: scheduled refresh. <strong id="EN-US_TOPIC_0000001764675134__b12164104471814">START WITH</strong> specifies the first refresh time. <strong id="EN-US_TOPIC_0000001764675134__b18901357161812">EVERY</strong> specifies the refresh interval. The value can be <strong id="EN-US_TOPIC_0000001764675134__b491110511910">MONTH</strong>, <strong id="EN-US_TOPIC_0000001764675134__b74702819191">DAY</strong>, <strong id="EN-US_TOPIC_0000001764675134__b1141181113194">HOUR</strong>, <strong id="EN-US_TOPIC_0000001764675134__b15285131321919">MINUTE</strong>, or <strong id="EN-US_TOPIC_0000001764675134__b1414711651915">SECOND</strong>.</p>
</li></ul>
</li><li id="EN-US_TOPIC_0000001764675134__li6623656101116"><strong id="EN-US_TOPIC_0000001764675134__b56171556141119">SET ( {storage_parameter = value} [, ... ] ) | RESET ( storage_parameter [, ... ] )</strong><p id="EN-US_TOPIC_0000001764675134__p1338375851119">Allows for setting table properties of materialized views. This syntax is supported only by clusters of 9.1.0.200 and later versions.</p>
<p id="EN-US_TOPIC_0000001764675134__p144441234181213">Parameters such as mv_pck_column, bitmap_columns, enable_foreign_table_query_rewrite, excluded_inactive_tables, force_rewrite_timeout and mv_analyze_mode can be set. For details, see <a href="dws_06_0357.html#EN-US_TOPIC_0000001811634773__section1561019065710">Parameter Description</a>.</p>
</li><li id="EN-US_TOPIC_0000001764675134__li155515426384"><strong id="EN-US_TOPIC_0000001764675134__b1077817381522">OWNER TO new_owner</strong><p id="EN-US_TOPIC_0000001764675134__p694034212386">Change the owner of a materialized view.</p>
</li></ul>
</div>
<div class="section" id="EN-US_TOPIC_0000001764675134__section1234154314474"><h4 class="sectiontitle">Examples</h4><p id="EN-US_TOPIC_0000001764675134__p16443112510351">Enable query rewriting for a materialized view.</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764675134__screen101461745357"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">mv1</span><span class="w"> </span><span class="n">ENABLE</span><span class="w"> </span><span class="n">QUERY</span><span class="w"> </span><span class="n">REWRITE</span><span class="p">;</span>
<span class="n">NOTICE</span><span class="p">:</span><span class="w"> </span><span class="n">REFRESH</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">should</span><span class="w"> </span><span class="n">be</span><span class="w"> </span><span class="n">executed</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="n">enable</span><span class="w"> </span><span class="n">query</span><span class="w"> </span><span class="n">rewrite</span><span class="p">.</span>
<span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span>
</pre></div></td></tr></table></div>
</div>
<p id="EN-US_TOPIC_0000001764675134__p96381654584">Modify the table properties of a materialized view.</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764675134__screen10846150285"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span>
<span class="normal">2</span>
<span class="normal">3</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">mv1</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="p">(</span><span class="n">force_rewrite_timeout</span><span class="o">=</span><span class="mi">100</span><span class="p">);</span>
<span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">mv1</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="p">(</span><span class="n">mv_pck_column</span><span class="o">=</span><span class="s1">'col1'</span><span class="p">);</span>
<span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">mv1</span><span class="w"> </span><span class="k">SET</span><span class="w"> </span><span class="p">(</span><span class="n">enable_foreign_table_query_rewrite</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">true</span><span class="p">);</span>
</pre></div></td></tr></table></div>
</div>
<p id="EN-US_TOPIC_0000001764675134__p161450252315">Change the refresh time of the materialized view.</p>
<div class="codecoloring" codetype="Sql" id="EN-US_TOPIC_0000001764675134__screen297744910317"><div class="highlight"><table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><span class="normal">1</span></pre></div></td><td class="code"><div><pre><span></span><span class="k">ALTER</span><span class="w"> </span><span class="n">MATERIALIZED</span><span class="w"> </span><span class="k">VIEW</span><span class="w"> </span><span class="n">mv1</span><span class="w"> </span><span class="n">REFRESH</span><span class="w"> </span><span class="k">START</span><span class="w"> </span><span class="k">WITH</span><span class="p">(</span><span class="s1">'2025-01-01 15:15:15'</span><span class="p">::</span><span class="n">timestamptz</span><span class="p">)</span><span class="w"> </span><span class="k">EVERY</span><span class="w"> </span><span class="p">(</span><span class="nb">interval</span><span class="w"> </span><span class="s1">'60 s'</span><span class="p">);</span>
</pre></div></td></tr></table></div>
</div>
</div>
<div class="section" id="EN-US_TOPIC_0000001764675134__section48851248561"><h4 class="sectiontitle">Helpful Links</h4><p id="EN-US_TOPIC_0000001764675134__p288518410563"><a href="dws_06_0357.html">CREATE MATERIALIZED VIEW</a>, <a href="dws_06_0360.html">DROP MATERIALIZED VIEW</a>, <a href="dws_06_0361.html">REFRESH MATERIALIZED VIEW</a></p>
</div>
</div>
<div>
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a href="dws_06_0118.html">DDL Syntax</a></div>
</div>
</div>
View Git Blame Copy Permalink