# 在线DDL
在线DDL支持对正常业务影响较小的情况下进行DDL，减少对业务的影响，提高业务运行的连续性和数据库的可用性。部分在线DDL只需要修改元数据，不涉及重建数据，操作时长较快，对业务影响较小。传统DDL操作若涉及重建数据，例如修改列类型、移动表空间、VACUUM FULL等操作，会对操作的表加长时间的排他锁，阻塞对该表的并发读写操作，影响用户业务运行。阻塞时长与数据量成正比，当数据量较大时，阻塞时长可达数小时、甚至数天。相比之下，在线DDL不会长时间加排他锁，执行中允许对该表的并发读写操作，减少对用户业务运行影响。
![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
- 使用DDL功能时，数据库需正常运行。
 
#### 使用在线DDL操作过程中进行并发读写操作
1. 创建表。 
   ```
   gaussdb=# CREATE TABLE t1(c1 INT,c2 INT);
   gaussdb=# INSERT INTO t1 VALUES(generate_series(1,10), generate_series(1,10));
   -- 查看表格
   gaussdb=# \d+ t1
                             Table "public.t1"
    Column |  Type   | Modifiers | Storage | Stats target | Description 
   --------+---------+-----------+---------+--------------+-------------
    c1     | integer |           | plain   |              | 
    c2     | integer |           | plain   |              | 
   Has OIDs: no
   Distribute By: HASH(c1)
   Location Nodes: ALL DATANODES
   Options: orientation=row, compression=no, storage_type=USTORE, segment=off
   ```
   
   
2. 历史版本已支持的在线DDL操作（仅部分示例，全部历史版本支持的在线DDL操作请参见《参考》中"SQL参考 \> SQL语法 \> Online DDL及其操作"）。 
   - 在线添加列。
     ```
     gaussdb=# ALTER TABLE t1 ADD COLUMN c3 integer;
     ALTER TABLE
     ```
     
   
   - 在线删除列。
     ```
     gaussdb=# ALTER TABLE t1 drop COLUMN c3;
     ALTER TABLE
     ```
     
   
   - 在线重命名表。
     ```
     gaussdb=# ALTER TABLE t1 RENAME TO t2;
     ALTER TABLE
     gaussdb=# ALTER TABLE t2 RENAME TO t1;
     ALTER TABLE
     ```
     
   
   
   
   
3. 设置ONLINE关键字并进行相关操作。 
   - 在线修改列类型。
     ```
     gaussdb=# ALTER TABLE ONLINE t1 ALTER c2 TYPE BIGINT;
     ALTER TABLE
     ```
     
   
   - 在线添加非空约束。
     ```
     gaussdb=# ALTER TABLE ONLINE t1 ALTER c1 SET NOT NULL;
     ALTER TABLE
     ```
     
   
   - 在线添加检查约束。
     ```
     gaussdb=# ALTER TABLE ONLINE t1 ADD CONSTRAINT c2_k CHECK(c2 > 0);
     ALTER TABLE
     ```
     
   
   - 在线修改添加唯一约束。
     ```
     gaussdb=# ALTER TABLE ONLINE t1 ADD UNIQUE(c1);
     NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "t1_c1_key" for table "t1"
     ALTER TABLE
     ```
     
   
   - 在线表级移动表空间。
     ```
     gaussdb=# CREATE TABLESPACE tbs RELATIVE LOCATION 'tablespace1/tbs';
     CREATE TABLESPACE
     gaussdb=# ALTER TABLE ONLINE t1 SET TABLESPACE tbs;
     ALTER TABLE
     ```
     
   
   - 以上在线DDL操作中，可进行并发读写操作。
     ```
     gaussdb=# INSERT INTO t1 VALUES(11, 11);
     INSERT 0 1
     ```
     
   
   
   
   
4. 查看表结构。 
   ```
   gaussdb=# \d+ t1
                             Table "public.t1"
    Column |  Type   | Modifiers | Storage | Stats target | Description 
   --------+---------+-----------+---------+--------------+-------------
    c1     | integer | not null  | plain   |              | 
    c2     | bigint  |           | plain   |              | 
   Indexes:
       "t1_c1_key" UNIQUE CONSTRAINT, ubtree (c1) WITH (storage_type=USTORE) TABLESPACE pg_default
   Check constraints:
       "c2_k" CHECK (c2 > 0)
   Has OIDs: no
   Tablespace: "tbs"
   Distribute By: HASH(c1)
   Location Nodes: ALL DATANODES
   Options: orientation=row, compression=no, storage_type=USTORE, segment=off
   ```
   
   
5. 使用VACUUM FULL在线回收表空间。 
   ```
   gaussdb=# VACUUM FULL t1 ONLINE;
   VACUUM
   ```
   
   
 
#### 使用分区在线DDL操作过程中进行并发读写操作
1. 创建分区表和普通表。 
   ```
   gaussdb=# CREATE TABLE t1(c1 INT,c2 INT) PARTITION BY RANGE (c2)
   (
       PARTITION P1 values less than (5),
       PARTITION P2 values less than (10),
       PARTITION P3 values less than (101)
   );
   gaussdb=# INSERT INTO t1 VALUES(generate_series(1,100), generate_series(1,100));
   gaussdb=# CREATE TABLE t1_exchange(c1 INT,c2 INT);
   gaussdb=# INSERT INTO t1_exchange VALUES(generate_series(10,50), generate_series(10,50));
   -- 查看表格
   gaussdb=# \d+ t1
                             Table "public.t1"
    Column |  Type   | Modifiers | Storage | Stats target | Description 
   --------+---------+-----------+---------+--------------+-------------
    c1     | integer |           | plain   |              | 
    c2     | integer |           | plain   |              | 
   Partition By RANGE(c2)
   Number of partitions: 3 (View pg_partition to check each partition range.)
   Has OIDs: no
   Options: orientation=row, compression=no, storage_type=USTORE, segment=off
   gaussdb=# \d+ t1_exchange
                             Table "public.t1_exchange"
    Column |  Type   | Modifiers | Storage | Stats target | Description 
   --------+---------+-----------+---------+--------------+-------------
    c1     | integer |           | plain   |              | 
    c2     | integer |           | plain   |              | 
   Has OIDs: no
   Options: orientation=row, compression=no, storage_type=USTORE, segment=off
   ```
   
   
2. 设置ONLINE关键字并进行相关操作。 
   - 在线合并分区。
     ```
     gaussdb=# ALTER TABLE ONLINE t1 MERGE PARTITIONS p1, p2 into PARTITION p2_new;
     ALTER TABLE
     gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='t1'::regclass AND parttype <> 'r' order by 1;
      relname | boundaries |   oid
     ---------+------------+---------
      p2_new  | {10}       | 4397673
      p3      | {101}      | 4397634
     (2 rows)
     ```
     
   
   - 在线切割分区。
     ```
     gaussdb=# ALTER TABLE ONLINE t1 SPLIT PARTITION p2_new AT (5) INTO (PARTITION P1, PARTITION P2);
     ALTER TABLE
     gaussdb=# SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='t1'::regclass AND parttype <> 'r' order by 1;
      relname | boundaries |   oid
     ---------+------------+---------
      p1      | {5}        | 4397706
      p2      | {10}       | 4397707
      p3      | {101}      | 4397634
     (3 rows)
     ```
     
   
   - 在线交换分区。
     ```
     gaussdb=# ALTER TABLE ONLINE t1 EXCHANGE PARTITION (p3) WITH TABLE t1_exchange;
     ALTER TABLE
     gaussdb=# SELECT COUNT(*) FROM t1 PARTITION (p3);
      count
     -------
         41
     (1 row)
     ```
     
   
   - 在线移动分区表空间。
     ```
     gaussdb=# CREATE TABLESPACE tbs RELATIVE LOCATION 'tablespace1/tbs';
     CREATE TABLESPACE
     gaussdb=# ALTER TABLE ONLINE t1 MOVE PARTITION p1 TABLESPACE tbs;
     ALTER TABLE
     gaussdb=# SELECT relname, spcname FROM pg_partition t1, pg_tablespace t2 WHERE T1.reltablespace=t2.oid and t1.parentid='t1'::regclass;
      relname | spcname
     ---------+---------
      p1      | tbs
     (1 row)
     ```
     
   
   - 以上在线DDL操作中，可进行并发读写操作。
     ```
     gaussdb=# INSERT INTO t1 VALUES(11, 11);
     INSERT 0 1
     gaussdb=# INSERT INTO t1_exchange VALUES(51, 51);
     INSERT 0 1
     ```
     
   
   
   
   
3. 查看表定义。 
   ```
   gaussdb=# \d+ t1
                             Table "public.t1"
    Column |  Type   | Modifiers | Storage | Stats target | Description 
   --------+---------+-----------+---------+--------------+-------------
    c1     | integer |           | plain   |              | 
    c2     | integer |           | plain   |              | 
   Partition By RANGE(c2)
   Number of partitions: 3 (View pg_partition to check each partition range.)
   Has OIDs: no
   Options: orientation=row, compression=no, storage_type=USTORE, segment=off
   gaussdb=# \d+ t1_exchange
                             Table "public.t1_exchange"
    Column |  Type   | Modifiers | Storage | Stats target | Description 
   --------+---------+-----------+---------+--------------+-------------
    c1     | integer |           | plain   |              | 
    c2     | integer |           | plain   |              | 
   Has OIDs: no
   Options: orientation=row, compression=no, storage_type=USTORE, segment=off
   gaussdb=# SELECT relname, reltablespace, boundaries FROM pg_partition;
    relname | reltablespace | boundaries
   ---------+---------------+------------
    t1      |             0 |
    p1      |       4397520 | {5}
    p2      |             0 | {10}
    p3      |             0 | {101}
   (4 rows)
   ```
   
   
4. 使用VACUUM FULL在线回收分区空间。 
   ```
   gaussdb=# VACUUM FULL t1 PARTITION (p1) ONLINE;
   VACUUM
   ```