云数据库 GAUSSDB-TRUNCATE:示例

时间：2024-07-01 18:36:25

云数据库 GAUSSDB T

示例

清理表数据

    
     
       
       
         --创建表reason。
gaussdb=# CREATE TABLE reason (r_reason_sk int,r_reason_id varchar(16),r_reason_desc varchar(100));

--向表中插入多条记录。
gaussdb=# INSERT INTO reason values(1,'AAAAAAAABAAAAAAA','reason 1'),  (5,'AAAAAAAABAAAAAAA','reason 2'), 
                                   (15,'AAAAAAAABAAAAAAA','reason 3'), (25,'AAAAAAAABAAAAAAA','reason 4'),
                                   (35,'AAAAAAAABAAAAAAA','reason 5'), (45,'AAAAAAAACAAAAAAA','reason 6');

--查看表的信息，大小约为8kB
gaussdb=# \d+
                                                       List of relations
 Schema |  Name  | Type  | Owner |    Size    |                             Storage                              | Description 
--------+--------+-------+-------+------------+------------------------------------------------------------------+-------------
 public | reason | table | omm1  | 8192 bytes | {orientation=row,compression=no,storage_type=USTORE,segment=off} | 
(1 row)

--使用DELETE语句不带WHERE条件，清空表的数据，并查看表的大小。
gaussdb=# DELETE FROM reason;
gaussdb=# \d+
                                                       List of relations
 Schema |  Name  | Type  | Owner |    Size    |                             Storage                              | Description 
--------+--------+-------+-------+------------+------------------------------------------------------------------+-------------
 public | reason | table | omm1  | 8192 bytes | {orientation=row,compression=no,storage_type=USTORE,segment=off} | 
(1 row)

--使用TRUNCATE清空表reason，并查看表的大小
gaussdb=# TRUNCATE TABLE reason;
gaussdb=# \d+
                                                     List of relations
 Schema |  Name  | Type  | Owner |  Size   |                             Storage                              | Description 
--------+--------+-------+-------+---------+------------------------------------------------------------------+-------------
 public | reason | table | omm1  | 0 bytes | {orientation=row,compression=no,storage_type=USTORE,segment=off} | 
(1 row)

--删除表。
gaussdb=# DROP TABLE reason;

        

      

    
   

清理分区表数据。

    
     
       
       
         --创建分区表。
gaussdb=# CREATE TABLE reason_p(
    r_reason_sk integer,
    r_reason_id character(16),
    r_reason_desc character(100)
)PARTITION BY RANGE (r_reason_sk)(
    partition p_05_before values less than (05),
    partition p_15 values less than (15),
    partition p_25 values less than (25),
    partition p_35 values less than (35),
    partition p_45_after values less than (MAXVALUE)
);

--插入数据。
gaussdb=# INSERT INTO reason_p values(1,'AAAAAAAABAAAAAAA','reason 1'),  (5,'AAAAAAAABAAAAAAA','reason 2'), 
                                     (15,'AAAAAAAABAAAAAAA','reason 3'), (25,'AAAAAAAABAAAAAAA','reason 4'),
                                     (35,'AAAAAAAABAAAAAAA','reason 5'), (45,'AAAAAAAACAAAAAAA','reason 6');

--清空分区p_05_before。
gaussdb=# ALTER TABLE reason_p TRUNCATE PARTITION p_05_before UPDATE GLOBAL INDEX;

--清空分区p_15。
gaussdb=# ALTER TABLE reason_p TRUNCATE PARTITION for (13) UPDATE GLOBAL INDEX;

--清空分区表。
gaussdb=# TRUNCATE TABLE reason_p;

--删除表reason_p。
gaussdb=# DROP TABLE reason_p;

        

      

    
   