forked from docs/doc-exports
luhuayi
177cd61a57
Reviewed-by: Pruthi, Vineet <vineet.pruthi@t-systems.com> Co-authored-by: luhuayi <luhuayi@huawei.com> Co-committed-by: luhuayi <luhuayi@huawei.com>
25 lines
6.0 KiB
HTML
25 lines
6.0 KiB
HTML
<a name="EN-US_TOPIC_0000002100746054"></a><a name="EN-US_TOPIC_0000002100746054"></a>
|
|
|
|
<h1 class="topictitle1">DDL Operations</h1>
|
|
<div id="body8662426"><div class="section" id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_section238722717463"><h4 class="sectiontitle">Suggestion 3.1: Avoiding Performing DDL Operations (Except CREATE) During Peak Hours or in Long Transactions</h4><div class="note" id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_note4102113972211"><img src="public_sys-resources/note_3.0-en-us.png"><span class="notetitle"> </span><div class="notebody"><p id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_p3291449204611"><strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b61999012510739">Impact of rule violation:</strong></p>
|
|
<p id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_p07452477220">DDL operations like <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b32591141172712">ALTER</strong>, <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b201072433277">DROP</strong>, <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b12333124582713">TRUNCATE</strong>, <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b132044852713">REINDEX</strong>, and <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b7261050102719">VACUUM FULL</strong> have high lock levels and can block services during execution.</p>
|
|
<ul id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_ul13291154984618"><li id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_li5931185322219">During peak hours, these DDL operations with high lock levels should be avoided to prevent service blockage.</li><li id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_li1498161415228">Long transactions involving DDL operations with held or waited locks can also block services.</li></ul>
|
|
<p id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_p1929111496465"><strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b1886099785112627">Solution:</strong></p>
|
|
<ul id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_ul45271052172"><li id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_li352714521971">Choose off-peak hours or maintenance windows for DDL operations based on service periods. Specify the DDL execution environment and time consumption to avoid service blockage due to long lock waiting duration.</li></ul>
|
|
</div></div>
|
|
</div>
|
|
<div class="section" id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_section13238718155015"><h4 class="sectiontitle">Rule 3.2: Specifying the Scope of Objects to Be Deleted When Using DROP</h4><div class="danger" id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_note981514283118"><span class="dangertitle"><img src="public_sys-resources/danger_3.0-en-us.png"> </span><div class="dangerbody"><p id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_p41292076312"><strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b96793526610739">Impact of rule violation:</strong></p>
|
|
<p id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_p1214422173919">Be cautious when using <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b20422205218017">DROP OBJECT</strong> (e.g., <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b3423952401">DATABASE</strong>, <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b12423205218013">USER/ROLE</strong>, <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b142315528017">SCHEMA</strong>, <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b1142395213016">TABLE</strong>, <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b2424145213013">VIEW</strong>) as it may cause data loss, especially with <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b342485211016">CASCADE</strong> deletions.</p>
|
|
<ul id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_ul1212967173115"><li id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_li2770216338"><strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b19982716218">DROP DATABASE</strong>: deletes all objects in the database.</li><li id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_li118232203334"><strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b106991316692">DROP USER</strong>: deletes the <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b0516341107">USER</strong> object and its schemas and table objects.</li><li id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_li14398524103317"><strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b167610919118">DROP SCHEMA</strong>: deletes all objects in the schema.</li><li id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_li10973122963314"><strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b6297022151112">DROP TABLE</strong>: deletes the <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b13415735191118">TABLE</strong> object and the indexes and views that depend on it.</li></ul>
|
|
<p id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_p1312916793117"><strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b1126046870112627">Solution:</strong></p>
|
|
<ul id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_ul0410457133717"><li id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_li5410165763716">Exercise caution when performing the <strong id="EN-US_TOPIC_0000002100746054__en-us_topic_0000002100047742_b9949120129">DROP</strong> operation and back up data in advance.</li></ul>
|
|
</div></div>
|
|
</div>
|
|
</div>
|
|
<div>
|
|
<div class="familylinks">
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="dws_04_0105.html">GaussDB(DWS) SQL Statement Development Specifications</a></div>
|
|
</div>
|
|
</div>
|
|
|