<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-online-ddl-single-multi">
      </a>
      17.12.6 Simplifying DDL Statements with Online DDL
     </h3>
    </div>
   </div>
  </div>
  <p>
   Before the introduction of
   <a class="link" href="glossary.html#glos_online_ddl" title="online DDL">
    online
      DDL
   </a>
   , it was common practice to combine many DDL operations
      into a single
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement. Because each
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statement involved copying and rebuilding the table, it was more
      efficient to make several changes to the same table at once, since
      those changes could all be done with a single rebuild operation
      for the table. The downside was that SQL code involving DDL
      operations was harder to maintain and to reuse in different
      scripts. If the specific changes were different each time, you
      might have to construct a new complex
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   for each slightly different scenario.
  </p>
  <p>
   For DDL operations that can be done online, you can separate them
      into individual
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statements for easier scripting and maintenance, without
      sacrificing efficiency. For example, you might take a complicated
      statement such as:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa5246341"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> i1<span class="token punctuation">(</span>c1<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ADD</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> i2<span class="token punctuation">(</span>c2<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">CHANGE</span> c4_old_name c4_new_name <span class="token datatype">INTEGER</span> <span class="token keyword">UNSIGNED</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   and break it down into simpler parts that can be tested and
      performed independently, such as:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa47106219"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> i1<span class="token punctuation">(</span>c1<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">INDEX</span> i2<span class="token punctuation">(</span>c2<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">CHANGE</span> c4_old_name c4_new_name <span class="token datatype">INTEGER</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You might still use multi-part
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   statements for:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Operations that must be performed in a specific sequence, such
          as creating an index followed by a foreign key constraint that
          uses that index.
     </p>
    </li>
    <li class="listitem">
     <p>
      Operations all using the same specific
      <code class="literal">
       LOCK
      </code>
      clause, that you want to either succeed or fail as a group.
     </p>
    </li>
    <li class="listitem">
     <p>
      Operations that cannot be performed online, that is, that
          still use the table-copy method.
     </p>
    </li>
    <li class="listitem">
     <p>
      Operations for which you specify
      <code class="literal">
       ALGORITHM=COPY
      </code>
      or
      <a class="link" href="server-system-variables.html#sysvar_old_alter_table">
       <code class="literal">
        old_alter_table=1
       </code>
      </a>
      , to force
          the table-copying behavior if needed for precise
          backward-compatibility in specialized scenarios.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
