达梦数据库7_第 3 章数据定义语句_3.6 管理表

3.6 管理表

3.6.1 基表定义语句

用户数据库建立后，就可以定义基表来保存用户数据的结构。达梦数据库中基表可以分为两类，分别为数据库表和外部表，数据库表由数据库管理系统自行组织管理，而外部表在数据库的外部组织，是操作系统文件。手册中如无明确说明基表均指数据库表。下面分别对这两类基表的创建与使用进行详细描述。

3.6.1.1 数据库基表

用户数据库建立后，就可以定义基表来保存用户数据的结构。需指定如下信息：

1. 表名、表所属的模式名；

2. 列定义；

3. 完整性约束。

语法格式

CREATE [[GLOBAL] TEMPORARY] TABLE <表名定义> <表结构定义>;

<表名定义> ::= [<模式名>.] <表名>

<表结构定义>::=<表结构定义 1> | <表结构定义 2>

<表结构定义 1>::= (<列定义> {,<列定义>} [,<表级约束定义>{,<表级约束定义>}]) [ON COMMIT <DELETE | PRESERVE> ROWS] [<PARTITION 子句>][<空间限制子句>] [<STORAGE 子句>][<压缩子句>] [<ROW MOVEMENT 子句>][<DISTRIBUTE 子句>]

<表结构定义 2>::= [ON COMMIT <DELETE | PRESERVE> ROWS] [<空间限制子句>][<STORAGE 子句>] [<压缩子句>]AS <不带 INTO 的 SELECT 语句>[<DISTRIBUTE 子句>];

<列定义> ::= <不同类型列定义> [<列定义子句>] [<STORAGE 子句>][<存储加密子句>]

<不同类型列定义> ::=<普通列定义>|<虚拟列列定义>

<普通列定义>::= <列名> <数据类型>

<虚拟列列定义> ::= <列名>[<数据类型>] [GENERATED ALWAYS]AS (<虚拟列定义>) [VIRTUAL]

[VISIBLE]

<列定义子句> ::=

DEFAULT <列缺省值表达式> |

<IDENTITY 子句> |

<列级约束定义> |

DEFAULT <列缺省值表达式> <列级约束定义> |

<IDENTITY 子句> <列级约束定义> |

<列级约束定义> DEFAULT <列缺省值表达式> |

<列级约束定义> <IDENTITY 子句>

<IDENTITY 子句> ::= IDENTITY [(<种子>,<增量>)]

<列级约束定义> ::= <列级完整性约束>{,<列级完整性约束>}

<列级完整性约束> ::= [CONSTRAINT <约束名>] < column_constraint_action>[<失效生效选项>]

< column_constraint_action>::=[NOT] NULL |<唯一性约束选项> [USING INDEX TABLESPACE {<表空间名> | DEFAULT}]|<引用约束> |CHECK (<检验条件>)|NOT VISIBLE

<唯一性约束选项> ::= [PRIMARY KEY ]| [[NOT] CLUSTER PRIMARY KEY] |[CLUSTER[UNIQUE] KEY] | UNIQUE |<引用约束> ::= [FOREIGN KEY] REFERENCES [PENDANT] [<模式名>.]<表名>[(<列名>{[,<

列名>]})] [MATCH <FULL|PARTIAL|SIMPLE>][<引用触发动作>] [WITH INDEX]

<引用触发动作> ::=

<UPDATE 规则> [<DELETE 规则>] |

<DELETE 规则> [<UPDATE 规则>]

<UPDATE 规则> ::= ON UPDATE <引用动作>

<DELETE 规则> ::= ON DELETE <引用动作>

<引用动作> ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION

<失效生效选项>::=ENABLE | DISABLE

<STORAGE 子句> ::= STORAGE(<STORAGE 项> {,<STORAGE 项>})

<STORAGE 项> ::=

[INITIAL <初始簇数目>] |

[NEXT <下次分配簇数目>] |

[MINEXTENTS <最小保留簇数目>] |

[ON <表空间名>] |

[FILLFACTOR <填充比例>]|

[BRANCH <BRANCH 数>]|

[BRANCH (<BRANCH 数>, <NOBRANCH 数>)]|

[NOBRANCH]|

[CLUSTERBTR]|

[WITH COUNTER]|

[WITHOUT COUNTER] |

[USING LONG ROW]

<存储加密子句> ::= <存储加密子句 1>|<存储加密子句 2>

<存储加密子句 1> ::= ENCRYPT [<加密用法>|<加密用法><加密模式>|<加密模式>]

<存储加密子句 2> ::= ENCRYPT { <加密用法>|<加密用法><加密模式>|<加密模式>}<散列选项>

<加密用法> ::= WITH <加密算法>

<加密模式> ::= <透明加密模式> | <半透明加密模式>

<透明加密模式> ::= AUTO

<半透明加密模式> ::= MANUAL</DI< div>

<散列选项> ::= HASH WITH [<密码引擎名>].<散列算法> [<加盐选项>]

<加盐选项> ::= [NO] SALT

<加密算法> ::= DES_ECB | DES_CBC | DES_CFB|DES_OFB|DESEDE_ECB|

DESEDE_CBC | DESEDE_CFB|DESEDE_OFB | AES128_ECB |

AES128_CBC | AES128_CFB | AES128_OFB | AES192_ECB |

AES192_CBC | AES192_CFB | AES192_OFB | AES256_ECB |

AES256_CBC | AES256_CFB | AES256_OFB | RC4

<散列算法> ::= MD5 | SHA1

<表级约束定义>::=[CONSTRAINT <约束名>] <表级约束子句>[<失效生效选项>]

<表级约束子句>::=<表级完整性约束>

<表级完整性约束> ::=

<唯一性约束选项> (<列名> {,<列名>}) [USING INDEX TABLESPACE{ <表空间名> |

DEFAULT}]|

FOREIGN KEY (<列名>{,<列名>}) <引用约束> |

CHECK (<检验条件>)

<PARTITION 子句> ::= PARTITION BY <PARTITION 项>

<PARTITION 项> ::=

RANGE (<列名>{,<列名>}) [INTERVAL <间隔表达式>] [<子分区模板>{,<子分区模

板>}](<RANGE 分区项> {,<RANGE 分区项>}) |

HASH (<列名>{,<列名>}) [<子分区模板>{,<子分区模板>}]PARTITIONS <分区数>

[<STORAGE HASH 子句>]|

HASH(<列名>{,<列名>})[<子分区模板>{,<子分区模板>}] (<HASH 分区项> {,<HASH 分

区项>})|

LIST(<列名>)[<子分区模板>{,<子分区模板>}](<LIST 分区项> {,<LIST 分区项>})|

COLUMN (<列名> {,<列名>}) [AS <别名>]|

COLUMN (<列分区项> {,<列分区项>})

<RANGE 分区项>::= PARTITION <分区名> VALUES [EQU OR] LESS THAN (<常量表达式|<

日期函数表达式>|MAXVALUE>{,<常量表达式|<日期函数表达式>|MAXVALUE>}) [<STORAGE 子

句>][<子分区描述项>]

< 日 期 函 数 表 达 式 > ::= <to_date 函数表达式 > | <to_datetime 函 数 表 达 式 > |

<to_timestamp 函数表达式>

<列分区项>::= (<列名>{,<列名>}) [AS <别名>] [<STORAGE 子句>]

<HASH 分区项>::= PARTITION <分区名> [<STORAGE 子句>][<子分区描述项>]

<LIST 分区项>::= PARTITION <分区名> VALUES (DEFAULT|<<表达式>,{<表达式>}>)

[<STORAGE 子句>][<子分区描述项>]

<子分区描述项> ::=

(<RANGE 子分区描述项>{,<RANGE 子分区描述项>})|

(<HASH 子分区描述项>{,<HASH 子分区描述项>})|

SUBPARTITIONS <分区数> [<STORAGE HASH 子句>]|

(<LIST 子分区描述项>{,<LIST 子分区描述项>})

<RANGE 子分区描述项> ::= <RANGE 子分区项>[<子分区描述项>]

<HASH 子分区描述项> ::= <HASH 子分区项>[<子分区描述项>]

<LIST 子分区描述项> ::= <LIST 子分区项>[<子分区描述项>]</DI< div>

<RANGE 子分区项> ::=

SUBPARTITION <分区名> VALUES [EQU OR] LESS THAN (<常量表达式|<日期函数表达

式>|MAXVALUE>){,<常量表达式|<日期函数表达式>|MAXVALUE>}) [<STORAGE 子句>]

<HASH 子分区项> ::= SUBPARTITION <分区名> [<STORAGE 子句>]

<LIST 子分区项> ::= SUBPARTITION <分区名> VALUES (DEFAULT|<<表达式>,{<表达式>}>)

[<STORAGE 子句>]

<间隔表达式> ::= <日期间隔函数> | <数值常量>

<子分区模板> ::= <RANGE 子分区模板>|<HASH 子分区模板>|<LIST 子分区模板>

<RANGE 子分区模板> ::= SUBPARTITION BY RANGE (<列名>{,<列名>})[SUBPARTITION

TEMPLATE (<RANGE 分区项> {,<RANGE 分区项>})]

<HASH 子分区模板> ::=

SUBPARTITION BY HASH (<列名>{,<列名>})SUBPARTITION TEMPLATE SUBPARTIONS

<分区数> [<STORAGE HASH 子句>]|

SUBPARTITION BY HASH (<列名>{,<列名>})SUBPARTITION TEMPLATE (<HASH 分区

项> {,<HASH 分区项>})

<LIST 子分区模板> ::= SUBPARTITION BY LIST (<列名>{,<列名>})[SUBPARTITION

TEMPLATE (<LIST 分区项> {,<LIST 分区项>})]

<STORAGE HASH 子句> ::= STORE IN (<表空间名列表>)

<空间限制子句> ::=

DISKSPACE LIMIT <空间大小>|

DISKSPACE UNLIMITED

<压缩子句> ::=

COMPRESS |

COMPRESS (<列名> {,<列名>}) |

COMPRESS EXCEPT (<列名> {,<列名>})

<ROW MOVEMENT 子句> ::=

ENABLE ROW MOVEMENT |

DISABLE ROW MOVEMENT

<DISTRIBUTE 子句> ::=

DISTRIBUTED [RANDOMLY | FULLY]|

DISTRIBUTED BY [<HASH>](<列名> {,<列名>})|

DISTRIBUTED BY RANGE (<列名> {,<列名>})(<范围分布项> {,<范围分布项>})|

DISTRIBUTED BY LIST (<<列名> {,<列名>}>)(<列表分布项> {,<列表分布项>})

<范围分布项> ::=

VALUES LESS THAN (<表达式>{,<表达式>}) ON <实例名>|

VALUES EQU OR LESS THAN (<表达式>{,<表达式>}) ON <实例名>

<列表分布项> ::= VALUES (<表达式>{,<表达式>}) ON <实例名>

<不带 INTO 的 SELECT 语句> ::= <查询表达式>|<带参数查询语句>

<带参数查询语句>::=<子查询>|(<带参数查询语句>)

参数

1. <模式名> 指明该表属于哪个模式，缺省为当前模式；
2. <表名> 指明被创建的基表名，基表名最大长度 128 字节；如果是分区表，主表名和分区名遵循“主表名+分区名”总长度不大于 128 字节。分区子表系统会默认命名，命名规则为：主表名_分区名，其中 hash 分区的分区子表命名规则是：基表名_DMHASHPART+分区序号；
3. <列名> 指明基表中的列名，列名最大长度 128 字节；
4. <数据类型> 指明列的数据类型；
5. <列缺省值表达式> 如果随后的 INSERT 语句省略了插入的列值，那么此项为列值指定一个缺省值，可以通过 DEFAULT 指定一个值。DEFAULT 表达式串的长度不能超过 2048 字节；
6. <列级完整性约束定义>中的参数：
     1) NULL 指明指定列可以包含空值，为缺省选项。
     2) NOT NULL 非空约束，指明指定列不可以包含空值；
     3) UNIQUE 唯一性约束，指明指定列作为唯一关键字；
     4) PRIMARY KEY 主键约束，指明指定列作为基表的主关键字；
     5) CLUSTER PRIMARY KEY 主键约束，指明指定列作为基表的聚集索引（也叫聚簇索引）主关键字；
     6) NOT CLUSTER PRIMARY KEY 主键约束，指明指定列作为基表的非聚集索引主关键字；
     7) CLUSTER KEY 指定列为聚集索引键，但是是非唯一的；
     8) CLUSTER UNIQUE KEY 指定列为聚集索引键，并且是唯一的；
     9) USING INDEX TABLESPACE <表空间名> 指定索引存储的表空间；
     10) REFERENCES 指明指定列的引用约束。引用约束要求引用对应列类型必须基本一致。所谓基本，是因为 CHAR 与 VARCHAR，BINARY 与 VARBINARY， TINYINT、SMALLINT 与 INT 在此被认为是一致的。如果有 WITH INDEX 选项，则为引用约束建立索引，否则不建立索引，通过其他内部机制保证约束正确性；
     11) CHECK 检查约束，指明指定列必须满足的条件；
     12) NOT VISIBLE 列不可见，当指定某列不可见时，使用 SELECT *进行查询时将不添加该列作为选择列。不可见列不能创建索引；使用 INSERT 无显式指定列列表进行插入时，值列表不能包含隐藏列的值。
7. <表级完整性约束>中的参数：
     1) UNIQUE 唯一性约束，指明指定列或列的组合作为唯一关键字；
     2) PRIMARY KEY 主键约束，指明指定列或列的组合作为基表的主关键字。指明 CLUSTER，表明是主关键字上聚集索引；指明 NOT CLUSTER，表明是主关键字上非聚集索引；
     3) USING INDEX TABLESPACE <表空间名> 指定索引存储的表空间；
     4) FOREIGN KEY 指明表级的引用约束，如果使用 WITH INDEX 选项，则为引用约束建立索引，否则不建立索引，通过其他内部机制保证约束正确性；
     5) CHECK 检查约束，指明基表中的每一行必须满足的条件；
     6) 与列级约束之间不应该存在冲突。
8. ON COMMIT<DELETE | PRESERVE>ROWS 用来指定临时表（TEMPORARY）中的数据是事务级或会话级的，缺省情况下是事务级。ON COMMIT DELETE ROWS：指定临时表是事务级的，每次事务提交或回滚之后，表中所有数据都被删除；ON COMMIT PRESERVE ROWS：指定临时表是会话级的，会话结束时才清空表；
9. <检验条件> 指明表中一列或多列能否接受的数据值或格式；
10. <查询表达式>和<子查询> 定义请查看数据查询章节；
11. <间隔表达式>中日期间隔函数为：NUMTOYMINTERVAL、NUMTODSINTERVAL；数值常量为：整型、DEC 类型。使用了<间隔表达式>的分区表称为间隔分区表。间隔分区表只能包含一个分区列，且该分区列类型为日期或数值。当对间隔分区表中的数据进行插入或更新操作时，若新的数据无法匹配现有的分区子表，则系统将自动以用户指定的现有分区的末尾临界值为起始值，以<间隔表达式>指定的值为间隔值创建一个可以匹配新数据的间隔分区。用户可通过查看系统表 SYSHPARTTABLEINFO 来获取新建分区的分区表 ID 以及分区名等信息。该功能可方便数据库管理员对分区表的管理。
12. STORAGE 项中： BRANCH、NOBRANCH 是堆表创建关键字，堆表为―扁平 B 树表‖。这两个参数用来指定堆表并发分支 BRANCH 和非并发分支 NOBRANCH 的数目。<BRANCH 数>取值范围为 1~64，<NOBRANCH 数>取值范围为 1~64。
     1）NOBRANCH：指定创建的表为堆表，并发分支个数为 0，非并发分支个数为 1；
     2）BRANCH（<BRANCH 数>, <NOBRANCH 数>）：指定创建的表为堆表，并发分支个数为<BRANCH 数>，非并发个数为<NOBRANCH 数>；
     3）BRANCH <BRANCH 数>：指定创建的表为堆表，并发分支个数为<BRANCH 数>，非并发分支个数为 0。 CLUSTERBTR：指定创建的表为非堆表，即普通 B 树表；
13. 组合水平分区表层次最多支持八层；
14. 多级分区表支持自定义子分区描述项，自定义的子分区描述项分区类型与分区列必须与子分区模板一致。如果子分区模板和自定义子分区描述项均指定了分区信息则按自定义分区描述项处理；
15. 垂直分区表不支持多级分区。普通表、堆表、列存储表均支持多级分区；
16. <ROW MOVEMENT 子句>仅对行存储的水平分区表有效，其它表类型自动忽略；
17. <虚拟列定义>指明定义虚拟列的表达式。

图例

col_define_low

语句功能

供 DBA 或具有 CREATE_TABLE 权限的用户定义基表。

使用说明

1. <表名>指定了所要建立的基表名。在一个<模式>中，<基表名>、<视图名>均不相同。如果<模式名>缺省，则缺省为当前模式。若指定 TEMPORARY，则表示该表为一个临时表，只在一个会话中有效，当一个会话结束，该临时表被自动清空。表名需要是合法的标识符，且满足 SQL 语法要求。当表名以―##‖开头时，则该表为全局临时表。

2. TEMPORARY 临时表不支持压缩 compress 功能。

3. GLOBAL 目前仅支持 GLOBAL 临时表，因此建临时表时是否指定 GLOBAL 效果是一样的。

4. 表名最大长度为 128 个字节。

5. 所建基表至少要包含一个<列名>指定的列，在一个基表中，各<列名>不得相同。一张基表中至多可以包含 2048 列。

6. 虚拟列上存在约束时，不支持使用 fldr 导入。

7. <DEFAULT 子句>指定列的缺省值，如：DEFAULT DATE '2015-12-26'。

8. 如果未指明 NOT NULL，也未指明<DEFAULT 子句>，则隐含为 DEFAULT NULL。

9. 自增列不能使用<DEFAULT 子句>。

10. <列缺省值表达式>的数据类型必须与本列的<数据类型>一致。缺省值表达式存在以下几点约束：

1) 仅支持只读系统函数或指定 FOR CALCULATE 创建的存储函数；
2) 不支持表列；
3) 不支持包变量或语句参数；
4) 不支持查询表达式；
5) 不支持 LIKE；
6) 不支持 CONTAINS 表达式。

11. 如果列定义为 NOT NULL，则当该列插入空值时会报错。

12. 约束被 DM 用来对数据实施业务规则，完成对数据完整性的控制。DM_SQL 中主要定义了以下几种类型的约束：非空约束、唯一性约束、主键约束、引用约束和检查约束。如果完整性约束只涉及当前正在定义的列，则既可定义成列级完整性约束，也可以定义成表级完整性约束；如果完整性约束涉及到该基表的多个列，则只能在语句的后面定义成表级完整性约束。

定义与该表有关的列级或表级完整性约束时，可以用 CONSTRAINT<约束名>子句对约束命名，系统中相同模式下的约束名不得重复。如果不指定约束名，系统将为此约束自动命名。经定义后的完整性约束被存入系统的数据字典中，用户操作数据库时，由 DBMS 自动检查该操作是否违背这些完整性约束条件。
1）非空约束主要用于防止向一列添加空值，这就确保了该列在表中的每一行都存在一个有意义的值。
a) 该约束仅用于列级。
b) 如果定义了列约束为 NOT NULL，则其<列缺省值表达式>不能将该列指定为 NULL。
c) 空值即为未知的值，没有大小，不可比较。除关键字列外，其列可以取空值。不可取空值的列要用 NOT NULL 进行说明。
2）唯一性约束主要用于防止一个值或一组值在表中的特定列里出现不止一次，确保数据的完整性。
a) 唯一性约束是通过唯一索引来实现的。创建了一个唯一索引也就创建了一个唯一性约束；同样的，创建了一个唯一性约束，也就同时创建了一个唯一索引，这种情况下唯一索引是由系统自动创建的。
b) NULL 值是不参加唯一性约束的检查的。DM 系统允许插入多个 NULL 值。对于组合的唯一性约束，只要插入的数据中涉及到唯一性约束的列有一个或多个 NULL 值，系统则认为这笔数据不违反唯一性约束。
3）主键约束确保了表中构成主键的一列或一组列的所有值是唯一的。主键主要用于识别表中的特定行。主键约束是唯一性约束的特例。
a) 可以指定多个列共同组成主键，最多支持 63 个列。
b) 主键约束涉及的列必须为非空。通常情况下，DM 系统会自动在主键约束涉及的列上自动创建非空约束。
c) 每个表中只能有一个主键。
d) 主键约束是通过创建唯一索引来实现的。DM 系统允许用户自己定义创建主键时，通过 CLUSTER 或 NOT CLUSTER 关键字来指明创建索引的类型。 CLUSTER 指明该主键是创建在聚集索引上的，NOT CLUSTER 指明该主
键是创建在非聚集索引上的。缺省情况下，主键是创建在非聚集索引上的。堆表和列存储表不允许建立聚集主键。
e) 表中不能同时包含聚集主键和多媒体数据类型列。
4）引用约束用于保证相关数据的完整性。引用约束中构成外键的一列或一组列，其值必须至少匹配其参照的表中的一行的一个主键或唯一键值。我们把这种数据的相关性称为引用关系，外键所在的表称为引用表，外键参照的表称为被引用表。
a) 引用约束指明的被引用表上必须已经建立了相关主键或唯一索引。也就是说，必须保持引用约束所引用的数据必须是唯一的。
b) 引用约束的检查规则：
i. 插入规则：外键的插入值必须匹配其被引用表的某个键值。
ii. 更新规则：外键的更新值必须匹配被引用表的某个键值。当修改被引用表中的主键值时，如果定义约束时的选项是 NO ACTION，且更新结果会违反引用约束则不允许更新；如果定义的是 SET NULL 则将引用表上的相关外键值置为 NULL；如果定义的是 CASCADE，那么引用表上的相关外键值将被修改为同样的值；如里定义的是 SET DEFAULT，则把引用列置为该列的缺省值。
iii. 删除规则：当从被引用表中删除一行数据时，如果定义约束时的选项是 NO ACTION，就不删除引用表上的相关外键值；如果定义的是 SET NULL 则将引用表上的相关外键值置为 NULL；如果定义的是 CASCADE，那么引用表上的相关外键值将被删除；如里定义的是 SET DEFAULT，则把每个引用列置为―<列缺省值表达式>‖规则中所指定的缺省值。
c) NULL 值不参加引用约束的检查。受引用约束的表，如果要插入的涉及到引用约束的列值有一个或多个 NULL 则认为插入值不违反引用约束。
d) MPP 环境下，引用列和被引用列都必需包含分布列，且分布情况完全相同。
e) MPP 环境下，不支持创建 SET NULL 或 SET DEFAULT 约束检查规则的引用约束。
5）检查约束用于对将要插入的数据实施指定的检查，从而保证表中的数据都符合指定的限制。<检验条件>必须是一个有意义的布尔表达式，其中的每个列名必须是本表中定义的列，但列的类型不得为多媒体数据类型，并且不应包含子查询、集函数。

13. 可以使用 PARTITION 子句指定水平分区和垂直分区，水平分区包括范围分区、哈希分区和列表分区三种。但是，系统不支持同时进行水平分区和垂直分区。

1) 范围（RANGE）分区：按照分区列的数据范围，确定实际数据存放位置的划分方式。
2) 哈希（HASH）分区：对分区列值进行 HASH 运算后，确定实际数据存放位置的划分方式，主要用来确保数据在预先确定数目的分区中平均分布，允许只建立一个 HASH 分区。
3) 列表（LIST）分区：通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。
4) 水平分区使用说明：
a) 分区列类型必须是数值型、字符型或日期型，不支持 BLOB、CLOB、IMAGE、 TEXT、LONGVARCHAR、BIT、BINARY、VARBINARY、LONGVARBINARY、BFILE、时间间隔类型、虚拟列和用户自定义类型为分区列。
b) 范围分区和哈希分区的分区键可以多个，最多不超过 16 列；LIST 分区的分区键必须唯一。
c) 范围分区支持 MAXVALUE 范围值的使用，MAXVALUE 是一个比任何值都大的值，但系统不会自动创建 MAXVALUE 分区。
d) 范围分区的范围值表达式类型应与分区列类型一致，否则按分区列类型进行类型转换。
e) LIST 分区支持 DEFAULT 的使用，所有不满足其他分区条件的数据，都划分为 DEFAULT 的分区，但系统不会自动创建 DEFAULT 分区。
f) 对于范围分区，增加分区必须在最后一个分区范围值的后面添加，要想在表的开始范围或中间增加分区，应使用 SPLIT PARTITION 语句。
g) 水平分区表指定主键和唯一约束时，分区键必须都包含在主键和唯一约束中，但是全局唯一索引不受此约束。
h) 在未指定 ENABLE ROW MOVEMENT 的水平分区表上执行更新分区键，不允许更新后数据发生跨分区的移动，即不能有行迁移。
i) 水平分区表不支持临时表。
j) 不能在水平分区表上建立自引用约束。
k) 普通环境中，水平分区表的各级分区数的总和上限是 65535；MPP 环境下，水平分区表的各级分区总数上限取决于 INI 参数 MAX_EP_SITES，上限为 2 (16 - log2MAX_EP_SITES)。比如：当 MAX_EP_SITES 为默认值 64
时，分区总数上限为 1024。
l) 可以定义主表的 BRANCH 选项，但不能对水平分区子表进行 BRANCH 项设置，子表的 BRANCH 项只能通过主表继承得到。
m) 水平分区表不支持自增列。
n) 不允许引用水平分区子表作为外键约束。
o) 水平分区子表默认命名规则：表名_分区名。
p) 水平分区子表删除后，会将子表上的数据一起删除。
5) 垂直分区使用说明：
a) 包含 IDENTITY 列的表不允许定义垂直分区表。
b) 垂直分区表不允许建立触发器。
c) 除 CLUSTER KEY 列外，其他任何列只能出现在一个分区中。
d) 垂直分区表上允许建立引用类型为 NO ACTION 的外键约束。
e) 垂直分区表上不允许建立除 UNIQUE 外的 CHECK 约束。
f) 不允许跨分区定义索引。
g) 一张垂直分区表最多允许有 32 个分区。
h) 禁止用户对垂直分区表子表的 INSERT/UPDATE/DELETE 直接操作，但
允许直接 SELECT。
i) 禁止通过 ALTER TABLE 方式为垂直分区表增加 PK 约束。
j) ALTER TABLE 目前只支持为主表的列添加默认值、ALTER TABLE RENAME 表名（主表或子表）和 DROP CONSTRAINT，其余均报错返回。
k) 垂直分区表不支持建表的 BRANCH 选项。
l) 垂直分区表上视图不支持更新 CLUSTER KEY 列。
m) 垂直分区表中所有的大字段列必须位于同一个子表中；所有的主键列必须位于同一个子表中；所有聚簇主键必须单独位于同一个子表中且不含有其它列。
n) 垂直分区表子表必须至少包含一个非 CLUSTER KEY 的列。
o) 垂直分区表在建表后不能更改聚集索引；如果建表时没有指定聚集索引，后续也不能再指定。
p) 垂直分区表上视图的 CHECK 约束不能跨分区，并且此时视图不能嵌套。
q) 垂直分区表不支持自引用约束。
r) 垂直分区表不支持 return into 功能。
s) 垂直分区子表命名规则：若未指定子表名称，则在主表名之后附加后缀 NDMPART，其中 N 为 00、01、02…；若指定子表名称，则子表名称就是指定的名字。

14. LIST 分区表使用说明：

1) LIST 水平分区表为堆表时，其各子表必须位于同一个表空间；
2) LIST 分区范围值不能为 NULL；
3) LIST 分区子表范围值个数与数据页大小和相关系统表列长度相关，存在以下限制：
a) 4K 页，单个子表最多支持 120 个范围值；
b) 8K 页，单个子表最多支持 254 个范围值；
c) 16K\32K 页，单个子表最多支持 270 个范围值；

15. 可以使用空间限制子句 DISKSPACE LIMIT 来限制表的最大存储空间，以 M 为单位，取值范围为 1 到 1048576，关键字 UNLIMITED 表示无限制。系统不支持查询建表情况下指定空间限制。

16. 可以使用 STORAGE 子句指定表的存储信息：

1) 初始簇数目：指建立表时分配的簇个数，必须为整数，最小值为 1，最大值为 256，缺省为 1。
2) 下次分配簇数目：指当表空间不够时，从数据文件中分配的簇个数，必须为整数，最小值为 1，最大值为 256，缺省为 1。
3) 最小保留簇数目：当删除表中的记录后，如果表使用的簇数目小于这个值，就不再释放表空间，必须为整数，最小值为 1，最大值为 256，缺省为 1。
4) 表空间名：在指定的表空间上建表，表空间必须已存在，缺省为该用户的默认表空间。
5) 填充比例：指定存储数据时每个数据页和索引页的充满程度，取值范围从 0到 100。默认值为 0，等价于 100，表示全满填充。插入数据时填充比例的值越低，可由新数据使用的空间就越多；更新数据时填充比例的值越大，更新
导致出现的页分裂的几率越大。同样，创建索引时，填充比例的值越低，可由新索引项使用的空间也就越多。
6) BRANCH 和 NOBRANCH：指定 BRANCH 和 NOBRANCH 的个数。
7) 区数：指定列存储表的区数，只对列存储表有效。
8) CLUSTERBTR：当 INI 参数 LIST_TABLE = 1 时，指定 CLUSTERBTR，则建立的表为普通 B 树表而非堆表。
9) WITH COUNTER：在表上维护当前表内的行数；WITHOUT COUNTER：表上只维护一个非实时地大概的行数。
对用户的影响：例如 select count(*) from test; 如果表 TEST 是 WITH COUNTER 属性，服务器直接取行数返回即可，可以快速响应；如果表 TEST 是 WITHOUT COUNTER 属性，服务器需要先扫描 b 树获取行数返回后才能响
应。不同的场景，根据需要灵活选择 COUNTER 属性。WITH COUNTER 属性可以通过 ALTER TABLE 语句修改。若省略该选项，默认是 WITH COUNTER 属性。
10) USING LONG ROW：支持超长记录存储。当 DM 行存储的记录长度超过页大小一半时，先尝试将过长的变长字符串转换为行外 BLOB 存储，如果转换后仍超长则报错。临时表、垂直分区表、huge 表、外部表不支持 USING LONG ROW 选项。水平分区子表的 USING LONG ROW 选项自动采用与主表保持一致的方式，两者不同的情况下，直接忽略水平分区子表的 USING LONG ROW 选项。

17. <压缩子句> 只是语法支持，功能已经取消。

18. 记录的列长度总和不超过块长的一半，VARCHAR 数据类型的长度是指数据定义长度，实际是否越界还需要判断实际记录的长度，而 CHAR 类型的长度是实际数据长度。与此类似的还有 VARBINARY 和 BINARY 数据类型。因此，对于 16K 的块，可以定义 CREATE TABLE TEST(C1 VARCHAR(8000),C2 INT)，但是不能定义 CREATE TABLE TEST(C1 CHAR(8000)，C2 INT)。

19. DM 具备自动断句功能。

20. 在对列指定存储加密属性时，用于保护用户的数据保存在物理介质之前使用指定的加密算法被加密，防止数据泄露。

21. 加密算法可以是系统中已经存在的算法名称，可选的算法可以在 v$ciphers 中获取，也可以使用第三方加密库中的算法，第三方加密库的实现可参考《DM 安全管理》的相关章节，将已实现的第三方加密动态库放到 bin 目录下的文件夹 external_crypto_libs 中，DM 支持加载多个第三方加密动态库，然后重启 DM 服务器即可引用其中的算法。

22. 散列算法。散列算法用于保证用户数据的完整性，若用户数据被非法修改，则能判断该数据不合法。加盐选项可以与散列算法配合使用。

23. 透明加密模式。用透明加密的方式加密列上的数据，在数据库中保存加密该列的密钥，执行 DML 语句的过程中自动获取密钥，仅在一些特殊的 DM 版本中允许用户指定透明加密密钥。

24. 可以使用 DISTRIBUTE 子句指定表的分布类型：

1）单机模式下建的分布表和普通表一样，但是不能创建指定实例名的分布表（如范围分布表和 LIST 分布表）。
2）在 MPP 模式下建分布表，如果未指定列则默认为 RANDOMLY(随机)分布表。
3）分布列类型不支持 BLOB、CLOB、IMAGE、TEXT、LONGVARCHAR、BIT、 BINARY、VARBINARY、LONGVARBINARY、时间间隔类型和用户自定义类型。
4） HASH 分布、RANGE 分布、LIST 分布允许更新分布列，并支持包含大字段列的表的分布列更新，但包含 INSTEAD OF 触发器的表、垂直分区表、堆表不允许更新分布列。
5）对于 FULLY（复制）分布表，只支持单表查询的更新和删除操作，并且查询项或者条件表达式中都不能包含 ROWID 伪列表达式。
6） RANGE（范围）分布表和 LIST（列表）分布表，分布列与分布列值列表必须一致，并且指定的实例名不能重复。
7）随机分布表不支持 UNIQUE 索引。

25. 虚拟列的使用：

虚拟列的值是不存储在磁盘上的，而是在查询的时候，根据定义的表达式临时计算后得到的结果。虚拟列可以用在查询、DML、DDL 语句中。索引可以建在虚拟列上。用户可以像使用普通列一样使用虚拟列。

1) GENERATED ALWAYS 和 VIRTUAL 为可选关键字，主要用于描述虚拟列的特性，写与不写没有本质区别。
2) 虚拟列中的 VISIBLE 只是语法支持，没有实际意义。
3) 不支持在索引表、外部表、临时表、垂直分区表上使用虚拟列。虚拟列和虚拟列中使用的列必须来自同一个表。表中至少要有一个非虚拟列。
4) 水平分区表可以使用虚拟列，但是不能作为分区列。
5) 在虚拟列上建索引相当于在表上建函数索引。
6) 即使表达式中的列已经有列级安全属性，虚拟列也不会继承列的安全规则。因此，为了保护虚拟列的数据，可以复制列级安全策略或者使用函数隐式来保护虚拟列数据。例如，信用卡的卡号被列级安全策略保护，只允许员工看到卡号的后四位。在这种情况下，可以把信用卡号的后四位定义成虚拟列。
7) 如果表达式是函数，则函数必须是确定性的函数。不允许使用分组函数、子查询（集函数、分析函数都不允许）。
8) 虚拟列不能作为分布表的分布列。
9) 虚拟列之间不能嵌套定义。
10) 虚拟列不能作为引用列。
11) 虚拟列最后的输出应该是标量性的。
12) 虚拟列不能是用户自定义类型、大字段类型。
13) 不能直接插入、更新虚拟列；但是可以在更新和删除的 WHERE 子句中使用虚拟列。
14) 不能删除被虚拟列引用的实际列。表中只有一个实际列时，这个实际列不能被删除。
15) 虚拟列不能被修改为实际列。实际列也不能被修改为虚拟列。
16) 虚拟列不能设置 default 值。
17) 虚拟列不能设置为 identity。
18) 不支持在虚拟列上建立位图连接索引和全文索引。
19) 不支持在虚拟列上建立物化视图日志。
20) 虚拟列不能在创建时作为 cluster PK、unique 或者 PK。但是可以在虚拟列上创建 unique index。
21) 虚拟列不支持 plus join（oracle 语法）。
22) 虚拟列不支持统计信息。
23) 虚拟列不支持加密。
24) 虚拟列的表达式定义长度不能超过 2048 字节。
25) Dm 支持系统内部函数，但是不能保证数据的确定性。
26) 虚拟列表达式中不支持 row like 表达式。
27) 虚拟列的表达式不支持 contains 表达式。
28) 更改表增加多列时，虚拟列表达式不能使用新增加的列。
29) 虚拟列上有约束时，使用 fldr 导入，将不能保证正确性。
30) 虚拟列上创建有索引时，则该虚拟列不支持修改列数据类型和列表达式。

举例说明

例 1 首先回顾一下第二章中定义的基表，它们均是用列级完整性约束定义的格式写出，也可以将唯一性约束、引用约束和检查约束以表级完整性约束定义的格式写出的。假定用户为 SYSDBA，下面以产品的评论表为例进行说明。

CREATE TABLE PRODUCTION.PRODUCT_REVIEW

(

PRODUCT_REVIEWID INT IDENTITY(1,1),

PRODUCTID INT NOT NULL,

NAME VARCHAR(50) NOT NULL,

REVIEWDATE DATE NOT NULL,

EMAIL VARCHAR(50) NOT NULL,

RATING INT NOT NULL,

COMMENTS TEXT,

PRIMARY KEY(PRODUCT_REVIEWID),

FOREIGN KEY(PRODUCTID) REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

CHECK(RATING IN(1,2,3,4,5))

);

--注：该语句的执行需在―产品的信息表‖已经建立的前提下

系统执行建表语句后，就在数据库中建立了相应的基表，并将有关基表的定义及完整性约束条件存入数据字典中。需要说明的是，由于被引用表要在引用表之前定义，本例中的产品的信息表被产品的评论表引用，所以这里应先定义产品的信息表，再定义产品的评论表，否则就会出错。

例 2 建表时指定存储信息，表 PERSON 建立在表空间 FG_PERSON 中，初始簇大小为 5，最小保留簇数目为 5，下次分配簇数目为 2，填充比例为 85。

CREATE TABLESPACE FG_PERSON DATAFILE 'FG_PERSON.DBF' SIZE 128;

CREATE TABLE PERSON.PERSON

( PERSONID INT IDENTITY(1,1) CLUSTER PRIMARY KEY,

SEX CHAR(1) NOT NULL,

NAME VARCHAR(50) NOT NULL,

EMAIL VARCHAR(50),

PHONE VARCHAR(25))

STORAGE

( INITIAL 5,

MINEXTENTS 5,

NEXT

2,

ON

FG_PERSON,

FILLFACTOR 85);

例 3 建立如下范围分区表后，表 PRODUCT_INVENTORY 将按照 QUANTITY 列值，被分成 4 个分区

CREATE TABLE PRODUCTION.PRODUCT_INVENTORY
(PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),
LOCATIONID INT NOT NULL REFERENCES PRODUCTION.LOCATION(LOCATIONID),
QUANTITY INT NOT NULL)
PARTITION BY RANGE (QUANTITY)
(
PARTITION P1 VALUES EQU OR LESS THAN (1),
PARTITION P2 VALUES EQU OR LESS THAN (100),
PARTITION P3 VALUES EQU OR LESS THAN (10000),
PARTITION P4 VALUES LESS THAN (MAXVALUE) --亦可将 MAXVALUE 替换成 99999
);

使用 SPLIT PARTITION 语句将上述范围表中的 P4 分区进行拆分，拆为 P5 和 P6 两个分区。拆完之后，该分区表有 P1、P2、P3、P5、P6 共 5 个分区。

alter table PRODUCTION.PRODUCT_INVENTORY SPLIT partition p4 at(20000) INTO(PARTITION P5,PARTITION P6);

例 4 建立如下垂直分区表后，系统内部将创建 3 张表，分别是垂直分区基表 ADDRESS，垂直分区子表 ADDRESS00DMPART 和 ADDRESS01DMPART。分区子表的命名规则是，在主表名之后附加后缀 NDMPART，其中 N 为 00、01、02…。

CREATE TABLE PERSON.ADDRESS

(ADDRESSID INT CLUSTER PRIMARY KEY,

ADDRESS1 VARCHAR(60) NOT NULL,

ADDRESS2 VARCHAR(60),

CITY VARCHAR(30) NOT NULL,

POSTALCODE VARCHAR(15) NOT NULL)

PARTITION BY COLUMN((ADDRESS1,ADDRESS2), (CITY, POSTALCODE));

例 5 建立如下范围分布表后，表 PRODUCT_INVENTORY 将按照 QUANTITY 列值，被分布到 2 个站点上。

CREATE TABLE PRODUCTION.PRODUCT_INVENTORY

(PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

LOCATIONID INT NOT NULL REFERENCES PRODUCTION.LOCATION(LOCATIONID),

QUANTITY INT NOT NULL)

DISTRIBUTED BY RANGE (QUANTITY)

(

VALUES EQU OR LESS THAN (100) ON EP01,

VALUES EQU OR LESS THAN (MAXVALUE) ON EP02

);

例 6 建立如下列表分布表后，表 PRODUCT_INVENTORY 将按照 LOCATIONID 列值，被分布到 2 个站点上，1，2，3，4 在 EP01 上，5，6，7，8 在 EP02，如果有插入其它值时则报错。

CREATE TABLE PRODUCTION.PRODUCT_INVENTORY

(PRODUCTID INT NOT NULL REFERENCES PRODUCTION.PRODUCT(PRODUCTID),

LOCATIONID INT NOT NULL REFERENCES PRODUCTION.LOCATION(LOCATIONID),

QUANTITY INT NOT NULL)

DISTRIBUTED BY LIST (LOCATIONID)

(

VALUES (1,2,3,4) ON EP01,

VALUES (5,6,7,8) ON EP02

);

例 7 建立如下复制分布表后，表 LOCATION 被分布到 MPP 各个站点上，每个站点上的数据都保持一致。

CREATE TABLE PRODUCTION.LOCATION

(LOCATIONID INT IDENTITY(1,1) PRIMARY KEY,

PRODUCT_SUBCATEGORYID INT NOT NULL,

NAME VARCHAR(50) NOT NULL)

DISTRIBUTED FULLY;

例 8 建立普通表查看 select count(*) 执行计划，再删除后重建表 test 带 without counter 属性，再查看执行计划：

SQL>create table test(c1 int);

操作已执行

已用时间:69.134(毫秒). 执行号:2422366.

SQL>explain select count(*) from test;

1 #NSET2: [0, 1, 0]

2 #PRJT2: [0, 1, 0]; exp_num(1), is_atom(FALSE)

3 #FAGR2: [0, 1, 0]; sfun_num(1)

已用时间:208.560(毫秒). 执行号:0.

SQL>drop table test;

操作已执行

已用时间:22.604(毫秒). 执行号:2446520.

SQL>create table test(c1 int) storage(without counter);

操作已执行

已用时间:198.693(毫秒). 执行号:2463231.

SQL>explain select count(*) from test;

1 #NSET2: [0, 1, 0]

2 #PRJT2: [0, 1, 0]; exp_num(1), is_atom(FALSE)

3 #AAGR2: [0, 1, 0]; grp_num(0), sfun_num(1)

4 #CSCN2: [0, 1, 0]; INDEX33555461(TEST)

已用时间:9.987(毫秒). 执行号:0.

3.6.1.2 外部表

需指定如下信息：

1. 表名、表所属的模式名；

2. 列定义；

3. 控制文件和数据文件所在目录。

语法格式

CREATE EXTERNAL TABLE <表名定义> <表结构定义>;

<表名定义> ::=[<模式名>.]<表名>

<表结构定义> ::= (<列定义> {,<列定义>}) <FROM 子句>
<列定义> ::= <列名> <数据类型>
<列定义> 参见 3.6.1.1 数据库基表说明
<FROM 子句> = <FROM 子句 1> | <FROM 子句 2>
<FROM 子句 1> ::= FROM <控制文件选项>
<FROM 子句 2> ::= FROM DATAFILE <数据文件选项> [<数据文件参数列表>]
<数据文件参数列表> ::= PARMS(<参数选项> {,<参数选项>})
<参数选项> ：：= FIELDS DELIMITED BY <表达式> |
RECORDS DELIMITED BY <表达式>|
ERRORS <n>|
BADFILE '<错误日志文件名称>'|
LOG '<日志文件名称>'|
NULL_STR <NULL 字符串>|
SKIP <跳过行数>|
CHARACTER_CODE <文件字符集>
<控制文件选项> ::= DEFAULT DIRECTORY <目录对象名> LOCATION ('<控制文件名>')
<数据文件选项> ::= DEFAULT DIRECTORY <目录对象名> LOCATION ('<数据文件名>')

参数

1. <模式名> 指明该表属于哪个模式，缺省为当前模式；
2. <表名> 指明被创建的外部基表名；
3. <列名> 指明基表中的列名；
4. <数据类型> 指明列的数据类型，暂不支持多媒体类型；
5. <参数选项>
   FIELDS 表示列分隔符；
   RECORDS 表示行分隔符，缺省为回车；
   ERRORS 表示忽略外部表数据转换中出现错误的行数，取值范围为大于 0 的正整数，缺省为 0，表示不忽略错误；此处 ERRORS 和控制文件中的 ERRORS 写一个就行，如果都写，以控制文件中的为主；
   BADFILE 表示错误日志文件（.bad 文件）名称，存放在指定目录下。缺省情况下，将在查询外部表时自动生成（若中途无错误数据，将不生成.bad 文件），缺省 BADFILE 文件前缀为“表名+表 id”；
   LOG 表示日志文件（.log 文件）名称，存放在指定目录下。缺省情况下，将在查询外部表时自动生成，缺省 LOG 文件前缀为“表名+表 id”；
   NULL_STR 指定数据文件中 NULL 值的表示字符串，默认忽略此参数；
   SKIP 指定跳过数据文件起始的逻辑行数，默认为 0；
   CHARACTER_CODE 指定数据文件中数据的编码格式，默认为 GBK，可选项有 GBK， TF-8，SINGLE_BYTE 和 EUC-KR；
6. <表达式> 字符串或十六进制串类型表达式，列分隔符只允许字符串类型；
7. <目录对象名> 指控制文件或数据文件所属的目录对象的名称

图例

语句功能

供 DBA 或具有 CREATE_TABLE 权限的用户定义外部基表。MPP 环境下不支持创建外部表。

使用说明

1. <表名>指定了所要建立的外部基表名。如果<模式名>缺省，则缺省为当前模式。表名需要是合法的标识符。且满足 SQL 语法要求；
2. 外部表的表名最大长度为 128 个字符；
3. 所建外部基表至少要包含一个<列名>指定的列，在一个外部基表中，各<列名>不得相同。一张外部基表中至多可以包含 2048 列；
4. 外部基表不能存在大字段列；
5. 外部基表不能存在任何约束条件；
6. 外部基表不能为临时表，不能建立分区
7. 外部基表上不能建立任何索引；
8. 外部基表是只读的，不存在表锁，不允许任何针对外部表的增删改数据操作，不允
许 truncate 外部表操作；
9. 用户在创建外部表之前，须先指定文件目录；
10. 用户创建外部表时必须具有指定目录的读权限；
11. 用户查询外部表时必须具有指定目录的读权限；由于用户查询外部表时，默认会在指定目录下生成日志文件和错误日志文件，因此用户还需具有指定目录的写权限；
12. 控制文件的格式为
[OPTIONS(
<id>=<value>
„„
)]
LOAD [DATA]
INFILE [<file_option>|<directory_option>]
[BADFILE <path_name>]
<into_table_clause>
<file_option>::= [LIST] <file_option 子句> [,<file_option 子句>]
<file_option 子句>::=<file_name> [<row_term_option>]
<file_name> ::=文件名称
<row_term_option> ::=STR [X] <delimiter>
<directory_option> ::= DIRECTORY <directory_name> [<row_term_option>]
<into_table_clause> ::= <into_table_single>{<into_table_single>}
<into_table_single> ::=INTO TABLE [<schema>.]<tablename>
 [FIELDS [TERMINATED BY] [X] <delimiter>]
<schema> ::=模式名
<tablename> ::=表名
<delimiter> ::='<字符串常量>'
其中OPTIONS选项为可选部分，目前OPTIONS中支持DATA、LOG、ERRORS、BADFILE、 NULL_STR、SKIP、CHARACTER_CODE 选项。DATA 表示数据文件名称，其余选项请参考
上述参数介绍部分。<file_name>和<directory_name>只能指定文件或文件夹名称，不能包含路径信息，否则报错
13.如果没有使用<参数选项>的 RECORDS 指定行分隔符，则在数据文件中的一行数据必须以回车结束；
14. 外部表支持查询 ROWID、USER 和 UID 伪列，不支持查询 TRXID 伪列。

举例说明

例 1 将文本文件作为控制文件以及数据文件，指定控制文件创建外部表。

编写数据文件（H:\DataBaseSystem\Dm7Dbms\ext_table）如下：

a|abc|varchar_data|12.34|12.34|12.34|12.34|0|1|1|1234|1234|1234|100|11|1234|1|1|14.2|12.1|12.1|1999-10-01|9:10:21|2002-12-12|15

编写控制文件（d:\ext_table\ctrl.txt）如下：

LOAD DATA INFILE 'data.txt'  INTO TABLE EXT FIELDS '|'

创建目录对象如下：

CREATE OR REPLACE DIRECTORY "EXTDIR" AS 'H:\DataBaseSystem\Dm7Dbms\ext_table';

建表语句：

DROP TABLE EXT;
CREATE EXTERNAL TABLE EXT (
	L_CHAR CHAR(1),
	L_CHARACTER CHARACTER(3),
	L_VARCHAR VARCHAR(20),
	L_NUMERIC NUMERIC(6,2),
	L_DECIMAL DECIMAL(6,2),
	L_DEC DEC(6,2),
	L_MONEY DECIMAL(19,4),
	L_BIT BIT,
	L_BOOL BIT,
	L_BOOLEAN BIT,
	L_INTEGER INTEGER,
	L_INT INT,
	L_BIGINT BIGINT,
	L_TINYINT TINYINT,
	L_BYTE BYTE,
	L_SMALLINT SMALLINT,
	L_BINARY BINARY,
	L_VARBINARY VARBINARY,
	L_FLOAT FLOAT,
	L_DOUBLE DOUBLE,
	L_REAL REAL,
	L_DATE DATE,
	L_TIME TIME,
	L_TIMESTAMP TIMESTAMP,
	L_INTERVAL INTERVAL YEAR
)FROM DEFAULT DIRECTORY EXTDIR LOCATION ('ctrl.txt');

系统执行建表语句后，就在数据库中建立了相应的外部基表。查询 ext_table 表：

SELECT * FROM EXT;

查询结果如下：

例 2 将文本文件作为数据文件，指定数据文件创建外部表。

编写数据文件（H:\DataBaseSystem\Dm7Dbms\ext_table\ext_table_2\data.txt）如下：

10|9|7
4|3|2

创建目录对象如下：

CREATE OR REPLACE DIRECTORY "EXTDIR_2" AS 'H:\DataBaseSystem\Dm7Dbms\ext_table\ext_table_2';

建表语句：

DROP TABLE EXT_TABLE2;

CREATE EXTERNAL TABLE EXT_TABLE2(C1 INT,C2 INT,C3 INT) FROM DATAFILE DEFAULT

DIRECTORY EXTDIR_2 LOCATION ('DATA.TXT') PARMS(FIELDS DELIMITED BY '|', RECORDS

DELIMITED BY 0X0D0A);

查询 ext_table2 表：

select * from ext_table2;

查询结果如下：

3.6.1.3 HUGE 表

语法格式

CREATE HUGE TABLE <表名定义> <表结构定义>[<PARTITION 子句>] [<STORAGE 子句 1>][<
压缩子句>] [<DISTRIBUTE 子句>][<日志属性>];
<表名定义> ::= [<模式名>.] <表名>
<表结构定义> ::=<表结构定义 1> | <表结构定义 2>
<表结构定义 1> ::= (<列定义> {,<列定义>} [<表级约束定义>{,<表级约束定义>}])
<表结构定义 2> ::= AS <不带 INTO 的 SELECT 语句>[<DISTRIBUTE 子句>]
<列定义> ::=<列名> <数据类型>[DEFAULT<列缺省值表达式>][<列级约束定义>][<STORAGE
子句 2>][<存储加密子句>]
<表级约束定义> ::= [CONSTRAINT <约束名>] <表级完整性约束>
<表级完整性约束> ::=
<唯一性约束选项> (<列名> {,<列名>}) [USING INDEX TABLESPACE <表空间名> | DEFAULT]|
FOREIGN KEY (<列名>{,<列名>}) <引用约束> |CHECK (<检验条件>)
<列级约束定义> ::= <列级完整性约束>{,<列级完整性约束>}
<列级完整性约束> ::= [CONSTRAINT <约束名>] [NOT] NULL |<唯一性约束选项> [USING
INDEX TABLESPACE <表空间名> | DEFAULT]
<唯一性约束选项> ::= [PRIMARY KEY] |UNIQUE
<存储加密子句> ::= <存储加密子句 1>|<存储加密子句 2>
<存储加密子句 1> ::= ENCRYPT [<加密用法>|<加密用法><加密模式>|<加密模式>]
<存储加密子句 2> ::= ENCRYPT { <加密用法>|<加密用法><加密模式>|<加密模式>}<散列选项>
<加密用法> ::= WITH <加密算法>
<加密模式> ::= <透明加密模式> | <半透明加密模式>
<透明加密模式> ::= AUTO [<加密密码 1> | <加密密码 2>]
<加密密码 1> ::= BY <口令>
<加密密码 2> ::= BY WRAPPED ‗<口令密文>‘
<半透明加密模式> ::= MANUAL
<散列选项> ::= HASH WITH [<密码引擎名>].<散列算法> [<加盐选项>]
<加盐选项> ::= [NO] SALT
<加密算法> ::= DES_ECB | DES_CBC | DES_CFB|DES_OFB|DESEDE_ECB|
 DESEDE_CBC | DESEDE_CFB|DESEDE_OFB | AES128_ECB |
 AES128_CBC | AES128_CFB | AES128_OFB | AES192_ECB |
 AES192_CBC | AES192_CFB | AES192_OFB | AES256_ECB |
 AES256_CBC | AES256_CFB | AES256_OFB | RC4
 <散列算法> ::= MD5 | SHA1
<PARTITION 子句> 参见 3.6.1.1 节
<STORAGE 子句 1> ::= STORAGE([SECTION (<区大小>),][FILESIZE (<文件大小>),][STAT
NONE,] [<WITH|WITHOUT> DELTA,] ON <HTS 表空间名>)
<STORAGE 子句 2> ::= STORAGE(STAT NONE)
<压缩子句> ::=
COMPRESS [LEVEL <压缩级别>] [FOR 'QUERY [LOW | HIGH]']|
COMPRESS [LEVEL <压缩级别>] [FOR 'QUERY [LOW | HIGH]'] (<列名> [LEVEL <
压缩级别>] [FOR 'QUERY [LOW | HIGH]'] {,<列名> [LEVEL <压缩级别>] [FOR 'QUERY [LOW 
| HIGH]'] })|
COMPRESS [LEVEL <压缩级别>] [FOR 'QUERY [LOW | HIGH]'] EXCEPT (<列名>{,<
列名>})
<DISTRIBUTE 子句> ::=
DISTRIBUTED [RANDOMLY | FULLY]|
DISTRIBUTED BY [HASH](<列名> {,<列名>})|
DISTRIBUTED BY RANGE (<列名> {,<列名>})(<范围分布项> {,<范围分布项>})|
DISTRIBUTED BY LIST (DEFAULT|<<列名> {,<列名>}>)(<列表分布项> {,<列表分布
项>})
<范围分布项> ::=
VALUES LESS THAN (<表达式>{,<表达式>}) ON <实例名>|
VALUES EQU OR LESS THAN (<表达式>{,<表达式>}) ON <实例名>
<列表分布项> ::= VALUES (<表达式>{,<表达式>}) ON <实例名>
<日志属性> ::=LOG NONE|LOG LAST|LOG ALL

参数

1. <表名> 指明被创建的 HUGE 表名。普通 HUGE 表，由于表名+辅助表名最大长度不大于 128 字节，则表名不大于 123 字节；分区 HUGE 表，由于子表名+辅助表名的长度不大于 128 字节，则子表名不大于 123 字节；
2. <区大小> 指一个区的数据行数。区的大小必须是 2 的多少次方，如果不是则向上对齐。取值范围：1024 行~1024*1024 行。不指定则默认值为 65536 行；
3. [STAT NONE] 指定不记录区统计信息，即在修改时不做数据的统计。不指定情况下，默认为做统计信息；
4. <HTS 表空间名> 指要创建的 HUGE 表所属的 HTS 表空间。不指定则存储于默认系统 HUGE 表空间 HMAIN 中；
5. < 文件大小 > 指创建 HUGE 表时指定的单个文件的大小。取值范围为 16M~1024*1024M。文件大小必须是 2 的多少次方，如果不是则向上对齐。不指定则默认为 64M；
6. <压缩级别> 为特定列指定通用压缩级别，有效值范围为：0~10，分别代表不同的压缩算法和压缩级别。有两种压缩算法：SNAPPY 和 ZIP。10 采用 SNAPPY 算法轻量级方式压缩。2~9 采用 ZIP 算法压缩，2~9 代表压缩级别，值越小表示压缩比越低、压缩速率越快；值越大表示压缩比越高、压缩速度越慢。0 和 1 快捷使用，默认值为 0。0 等价于 LEVEL 2；1 等价于 LEVEL 9；
7. FOR 'QUERY [LOW]'表示进行规则压缩；FOR 'QUERY HIGH'表示结合进行规则压缩与通用压缩结合，前者的压缩比一般在 1:1 至 1:3 之间，后者一般为 1:3 至 1:5 之间。规则压缩方式一般适用于具有一定的数据规则的数据的压缩，例如重复值较多等。若某列的类型为字符串类型且定义长度超过 48，则即使指定规则压缩也无效，实际只进行通用压缩；
8. <日志属性> 此属性仅对非事务型 HUGE 表有效，支持通过做日志来保证数据的完整性。完整性保证策略主要是通过数据的镜像来实现的，镜像的不同程度可以实现不同程度的完整性恢复。
三种选择：
1）LOG NONE：不做镜像。相当于不做数据一致性的保证，如果出错只能手动通过系统函数 SF_REPAIR_HFS_TABLE(模式名,表名)来修复表数据。
2）LOG LAST：做部分镜像。但是在任何时候都只对当前操作的区做镜像，如果当前区的操作完成了，这个镜像也就失效了，并且可能会被下一个被操作区覆盖，这样做的好处是镜像文件不会太大，同时也可以保证数据是完整的。但有可能遇到的问题是：一次操作很多的情况下，有可能一部分数据已经完成，另一部分数据还没有来得及做的问题。
3）LOG ALL：全部做镜像。在操作过程中，所有被修改的区都会被记录下来，当一次操作修改的数据过多时，镜像文件有可能会很大，但能够保证操作完整性。默认选择为 LOG LAST;
9. <WITH|WITHOUT> DELTA WITH DELTA 表示创建事务型 HUGE 表；WITHOUT DELTA 表示创建非事务型 HUGE 表，缺省为 WITHOUT DELTA。

图例

语句功能

供 DBA 或具有 CREATE HUGE TABLE 权限的用户创建 HUGE 表。

使用说明

1．非事务型 HUGE 表的插入、删除与更新操作处理都不能进行回滚；
2．建 HUGE 表时仅支持定义 NULL、NOT NULL、UNIQUE 约束以及 PRIMARY KEY，后两种约束也可以通过 ALTER TABLE 的方式添加，但这两种约束不检查唯一性；
3．HUGE 不允许建立聚簇索引，允许建立二级索引，不支持建位图索引，其中 UNIQUE 索引不检查唯一性；
4．不支持 SPACE LIMIT（空间限制）；
5．不支持建立全文索引；
6．不支持使用自定义类型；
7．不支持引用约束；
8．不支持 IDENTITY 自增列；
9．不支持大字段列；
10．不支持建触发器；
11．不允许垂直分区；
12．不支持游标的修改操作；
13．PK 和 UNIQUE 约束不检查唯一性，对应的索引都为虚索引；UNIQUE 索引也不检查唯一性，为实索引，索引标记中不包含唯一性标记，即和普通二级索引相同；
14．不允许对分区子表设置 SECTION 和 WITH/WITHOUT DELTA；
15．对于非事务型 HUGE 表，若指定记录区统计信息，可能因为统计信息超长造成记录插入或更新失败；
16．HUGE 表备份还原请参考附录 3。

举例说明

例以 SYSDBA 身份登录数据库后，创建 HUGE 表 orders。

CREATE HUGE TABLE orders
(
o_orderkey INT,
o_custkey INT,
o_orderstatus CHAR(1),
o_totalprice FLOAT,
o_orderdate DATE,
o_orderpriority CHAR(15),
o_clerk CHAR(15),
o_shippriority INT,
o_comment VARCHAR(79) STORAGE(stat none)
)STORAGE(SECTION(65536), FILESIZE(64), WITH DELTA, ON HTS_NAME) COMPRESS
LEVEL 9 FOR 'QUERY HIGH' (o_comment);

这个例子创建了一个名为 ORDERS 的事务型 HUGE 表，ORDERS 表的区大小为 65536 行，文件大小为 64M，指定所在的表空间为 HTS_NAME，o_comment 列指定的区大小为不做统计信息，其它列（默认）都做统计信息，指定列 o_comment 列压缩类型为查询高压缩率，压缩级别为 9。

posted @ 2024-01-15 17:05 一品堂.技术学习笔记阅读(412) 评论(0) 编辑收藏举报

刷新页面返回顶部

✧꧁一品堂.技术学习笔记꧂✧.

达梦数据库7_第 3 章数据定义语句_3.6 管理表

3.6 管理表

3.6.1 基表定义语句

3.6.1.1 数据库基表

语法格式

参数

图例

语句功能

使用说明

举例说明

例 1 首先回顾一下第二章中定义的基表，它们均是用列级完整性约束定义的格式写出，也可以将唯一性约束、引用约束和检查约束以表级完整性约束定义的格式写出的。假定用户为 SYSDBA，下面以产品的评论表为例进行说明。

例 2 建表时指定存储信息，表 PERSON 建立在表空间 FG_PERSON 中，初始簇大小为 5，最小保留簇数目为 5，下次分配簇数目为 2，填充比例为 85。

例 3 建立如下范围分区表后，表 PRODUCT_INVENTORY 将按照 QUANTITY 列值，被分成 4 个分区

例 4 建立如下垂直分区表后，系统内部将创建 3 张表，分别是垂直分区基表 ADDRESS，垂直分区子表 ADDRESS00DMPART 和 ADDRESS01DMPART。分区子表的命名规则是，在主表名之后附加后缀 NDMPART，其中 N 为 00、01、02…。

例 5 建立如下范围分布表后，表 PRODUCT_INVENTORY 将按照 QUANTITY 列值，被分布到 2 个站点上。

例 6 建立如下列表分布表后，表 PRODUCT_INVENTORY 将按照 LOCATIONID 列值，被分布到 2 个站点上，1，2，3，4 在 EP01 上，5，6，7，8 在 EP02，如果有插入其它值时则报错。

例 7 建立如下复制分布表后，表 LOCATION 被分布到 MPP 各个站点上，每个站点上的数据都保持一致。

例 8 建立普通表查看 select count(*) 执行计划，再删除后重建表 test 带 without counter 属性，再查看执行计划：

3.6.1.2 外部表

需指定如下信息：

语法格式

参数

图例

语句功能

使用说明

举例说明

例 1 将文本文件作为控制文件以及数据文件，指定控制文件创建外部表。

例 2 将文本文件作为数据文件，指定数据文件创建外部表。

3.6.1.3 HUGE 表

语法格式

参数

图例

语句功能

使用说明

举例说明

例以 SYSDBA 身份登录数据库后，创建 HUGE 表 orders。

公告

✧*꧁一品堂.技术学习笔记꧂*✧.

达梦数据库7_第 3 章 数据定义语句_3.6 管理表

3.6 管理表

3.6.1 基表定义语句

3.6.1.1 数据库基表

语法格式

参数

图例

语句功能

使用说明

举例说明

例 1 首先回顾一下第二章中定义的基表，它们均是用列级完整性约束定义的格式写出， 也可以将唯一性约束、引用约束和检查约束以表级完整性约束定义的格式写出的。假定用户 为 SYSDBA，下面以产品的评论表为例进行说明。

例 2 建表时指定存储信息，表 PERSON 建立在表空间 FG_PERSON 中，初始簇大小为 5，最小保留簇数目为 5，下次分配簇数目为 2，填充比例为 85。

例 3 建立如下范围分区表后，表 PRODUCT_INVENTORY 将按照 QUANTITY 列值，被 分成 4 个分区

例 4 建立如下垂直分区表后，系统内部将创建 3 张表，分别是垂直分区基表 ADDRESS， 垂直分区子表 ADDRESS00DMPART 和 ADDRESS01DMPART。分区子表的命名规则是，在主 表名之后附加后缀 NDMPART，其中 N 为 00、01、02…。

例 5 建立如下范围分布表后，表 PRODUCT_INVENTORY 将按照 QUANTITY 列值，被 分布到 2 个站点上。

例 6 建立如下列表分布表后，表 PRODUCT_INVENTORY 将按照 LOCATIONID 列值， 被分布到 2 个站点上，1，2，3，4 在 EP01 上，5，6，7，8 在 EP02，如果有插入其它 值时则报错。

例 7 建立如下复制分布表后，表 LOCATION 被分布到 MPP 各个站点上，每个站点上 的数据都保持一致。

例 8 建立普通表查看 select count(*) 执行计划，再删除后重建表 test 带 without counter 属性，再查看执行计划：

3.6.1.2 外部表

需指定如下信息：

语法格式

参数

图例

语句功能

使用说明

举例说明

例 1 将文本文件作为控制文件以及数据文件，指定控制文件创建外部表。

例 2 将文本文件作为数据文件，指定数据文件创建外部表。

3.6.1.3 HUGE 表

语法格式

参数

图例

语句功能

使用说明

举例说明

例 以 SYSDBA 身份登录数据库后，创建 HUGE 表 orders。

公告

✧꧁一品堂.技术学习笔记꧂✧.

达梦数据库7_第 3 章数据定义语句_3.6 管理表

例 1 首先回顾一下第二章中定义的基表，它们均是用列级完整性约束定义的格式写出，也可以将唯一性约束、引用约束和检查约束以表级完整性约束定义的格式写出的。假定用户为 SYSDBA，下面以产品的评论表为例进行说明。

例 3 建立如下范围分区表后，表 PRODUCT_INVENTORY 将按照 QUANTITY 列值，被分成 4 个分区

例 4 建立如下垂直分区表后，系统内部将创建 3 张表，分别是垂直分区基表 ADDRESS，垂直分区子表 ADDRESS00DMPART 和 ADDRESS01DMPART。分区子表的命名规则是，在主表名之后附加后缀 NDMPART，其中 N 为 00、01、02…。

例 5 建立如下范围分布表后，表 PRODUCT_INVENTORY 将按照 QUANTITY 列值，被分布到 2 个站点上。

例 6 建立如下列表分布表后，表 PRODUCT_INVENTORY 将按照 LOCATIONID 列值，被分布到 2 个站点上，1，2，3，4 在 EP01 上，5，6，7，8 在 EP02，如果有插入其它值时则报错。

例 7 建立如下复制分布表后，表 LOCATION 被分布到 MPP 各个站点上，每个站点上的数据都保持一致。

例以 SYSDBA 身份登录数据库后，创建 HUGE 表 orders。