达梦数据库的管理表

管理表达梦数据库有限公司2021年5月22目录1 指定表的聚集索引键... 12 查询建表... 13 临时表的使用... 33.1 临时表的规则... 33.2 临时表的两种生命周期模式... 43.3 例子... 44 修改表... 55 清空表... 66 查看表信息... 96.1 查看表定义... 96.2 查看自增列信息... 96.3 查看表空间使用情况... 10指定表的聚集索引键索引组织表都有一个聚簇索引默认情况下逻辑ROWID叫做它的聚集索引键其叶子节点按着聚集索引键的大小顺序来相连它最主要的作用是可以根据“聚集索引键”快速找到整行数据且不需要回表。很多情况下以ROWID建的默认聚集索引并不能提高查询速度因为实际情况下很少人根据ROWID来查找数据。因此DM提供三种方式供用户指定聚集索引键1.CLUSTERPRIMARYKEY指定列为聚集索引键并同时指定为主键称为聚集主键2.CLUSTERKEY指定列为聚集索引键但是是非唯一的3.CLUSTERUNIQUEKEY指定列为聚集索引键并且是唯一的。举一个例子来说明功效CREATETABLESTUDENT(STUNOINTCLUSTERPRIMARYKEY,STUNAMEVARCHAR(15)NOTNULL,TEANOINT,CLASSIDINT);上面的sql是一个创建表的语句它的聚集索引键是我们的主键stuno。如果现在的聚集索引键是ROWID那假如我们使用精确的stuno来查询数据只能进行全表扫描但是当我们修改聚集索引键为stuno之后我们使用精确的stuno走的是B树时间复杂度大大降低了。查询建表CTAS用于创建一个与已有表相同的新表或者为了创建一个只包含另一个表的一些行和列的新表。最简单使用方法如下。CREATETABLENEW_EMPASSELECT*FROMEMPLOYEE;在默认情况下CTAS只复制数据而不是复制结构。但是达梦为他进行了扩展通过一个参数来改变它是否迁移结构。该参数为CTAB_SEL_WITH_CONS。当CTAB_SEL_WITH_CONS0时拷贝数据以及显式写的NUTNULL和自增列其余都不要为1时叫做“列属性”模式对每列的各种属性比如默认值主键检查约束唯一约束等全部复制过来为2时叫做“完全克隆”模式表级的所有信息都拷贝相当于完全复制包括索引等。同时对于比较复杂的查询建表有针对非空约束的判断。原则为保证可以复制数据。规则如下计算列集合查询非外连接。源表是怎么样的那就同样是怎么样因为他们并不会无中生有的产生NULL。对于外连接。我们都知道外连接的时候是可能产生NULL的。所以需要新增以下规则。NOTNULL列来自主表左表的左列/右表的右列这时候因为不会产生NULL跟上面一样。NOTNULL列来自非主表那只能是NULL因为如果不这样可能会因为产生的NULL导致插入不了数据这是决不允许的。下面试验一下CTAB_SEL_WITH_CONS参数对CTAS的影响。我创建表的DDL如下CREATETABLESRC(IDINTPRIMARYKEY,NAMEVARCHAR(20)NOTNULL,CREATE_DATEDATEDEFAULTSYSDATE);INSERTINTOSRCVALUES(1,张三,SYSDATE);假如我该参数为0可以看到NUTNULL过来了但是主键没有过来。改成一之后再重新试一次。可以看到后面多了一条将我们的ID变为非聚集主键的语句跟源表的完全一致。临时表的使用临时表的规则临时表的规则临时表的结构是数据库级别的只要create一次永久有效直到主动drop事务提交后或者会话断开后表数据自动清空这取决于临时表的生命周期模式但表结构还在可以再次插入新数据。临时表中的数据绝对隔离只有会话自己可以看到其他任何会话绝对看不到。临时表的空间是用到才给当插入数据时才真正分配磁盘空间临时表的视图会存结果集使得它的速度更加快。临时表产生的redo日志更少性能更好。临时表的两种生命周期模式事务级关键词为ONCOMMITDELETEROWS每次执行commit或者rollback即事务结束时就会清空数据。会话级关键词为ONCOMMITPRESERVEROWS事务结束不清空而连接断开时清空。例子事务级临时表两个会话会话A和会话B会话A创建临时表CREATEGLOBALTEMPORARYTABLEtemp_emp(emp_idINT,emp_nameVARCHAR(50))ONCOMMITDELETEROWS;会话B可以使用并且增加的数据仅会话B自己可见会话A不可见。INSERTINTOtemp_empVALUES(1,张三);SELECT*fromTEMP_EMP;提交完之后再查数据被清空了COMMIT;SELECT*fromTEMP_EMP;会话级临时表可以看到当提交之后数据依旧存在。--1.创建会话级临时表需显式指定CREATEGLOBALTEMPORARYTABLEtmp_session(idINT,nameVARCHAR(30))ONCOMMITPRESERVEROWS;--2.插入数据INSERTINTOtmp_sessionVALUES(1,会话数据B);COMMIT;--提交事务--3.提交后再次查询数据依然存在SELECT*FROMtmp_session;--返回1行可以看到“会话数据B”--4.退出当前会话断开连接数据被清空--重新连接后再次查询SELECT*FROMtmp_session;--返回0行数据已随会话结束而清空修改表表如果在当前用户自己的模式下需要altertable权限但是假如在其他模式下需要alteranytable权限。可以对表的列约束触发器资源限制自增列等都进行修改。其中比较重要的是添加列与ALTER_TABLE_OPT参数。ALTER_TABLE_OPT这个参数影响添加列的实现方式每种方式的效率和具体过程下面讲解。参数1查询插入。这是最傻瓜式的操作。新建一张表查询原表然后一行一行插入到新表相当于是建了一个新表。无论是索引组织表还是堆表ROWID都可能改变。参数2开启快速列但有条件。假如新列没有默认值或者默认值为NULL。系统可以瞬间加列此时ROWID不变但是假如有非NULL的默认值退化为参数1的查询插入模式ROWID依旧可能改变。参数3开启快速列并且允许指定默认值。它的实现方式是即使新列有非NULL默认值系统也只在登记簿里记下默认值。查询旧数据时系统自动把默认值补上返回给你HUGE表HUGE因为比较特殊所以无论是哪个参数全是查询插入模式。清空表清空表区分一下三种方式就行了DeletedropcreatetruncateDelete是DML语句。它先定位到相应位置然后执行删除同时会产生大量的REDO日志和UNDO日志所以可以回滚。DROPCREATE是DDL操作。本质就是先用DROP清空然后用CREATE新建一个空表。他会把表级别的列、键、约束、索引和触发器全部删掉并且基于该表的视图、存储过程变为无效INVALID,针对该表的授权被删除。TRUNCATE可以快速有效的删除表所有行他是DDL语句不是DML语句所以他不会触发DML对应的触发器。它不产生回滚信息会立即释放原来分配给该表的空间。一般都可以成功但是如果要清空的表被其他表引用并且子表不为空或子表的外键约束未被禁用则不能TRUNCATE该表。它的实现方式为直接更换数据段然后将原本的数据段空间标记为空闲。例子1DELETE可回滚TRUNCATE不可回滚。--准备数据DROPTABLEIFEXISTSt1;CREATETABLEt1(idINT,nameVARCHAR(20));INSERTINTOt1VALUES(1,A),(2,B),(3,C);COMMIT;--用DELETE清空DELETEFROMt1;SELECTCOUNT(*)FROMt1;--结果0--回滚ROLLBACK;SELECTCOUNT(*)FROMt1;--结果3数据回来了--再用TRUNCATE清空TRUNCATETABLEt1;SELECTCOUNT(*)FROMt1;--结果0--回滚ROLLBACK;SELECTCOUNT(*)FROMt1;--结果0回不来了因为delete记录了undo日志所以可以回滚。Truncate回滚不会报错但是数据不会有任何改变例子2DELETE触发行级触发器TRUNCATE不触发--建日志表DROPTABLEIFEXISTSt2_log;CREATETABLEt2_log(msgVARCHAR(50));--建主表DROPTABLEIFEXISTSt2;CREATETABLEt2(idINT);--建行级DELETE触发器CREATEORREPLACETRIGGERtrg_t2_delAFTERDELETEONt2FOREACHROWBEGININSERTINTOt2_logVALUES(删了一行:||:OLD.id);END;/--插入3行INSERTINTOt2VALUES(1),(2),(3);COMMIT;--用DELETE清空DELETEFROMt2;SELECT*FROMt2_log;--结果3条日志每删一行触发一次--清空日志表TRUNCATETABLEt2_log;--重新插入数据INSERTINTOt2VALUES(1),(2),(3);COMMIT;--用TRUNCATE清空TRUNCATETABLEt2;SELECT*FROMt2_log;--结果0条日志触发器没被触发结果上DELETE会输出三条日志而TRUNCATE没有日志查看表信息查看表定义其实本质就是查看表的DDL语句使用CALLSP_TABLEDEF函数。简单来例子可以看下面这条语句。CALLSYS.SP_TABLEDEF(DMTEST,DEPT);如果有图形化界面也可以用下面的方式来查看自增列信息下面举的例子完美说明了用法。CREATETABLEIDENT_TABLE(C1INTIDENTITY(100,100),C2INT);--插入第一行只给C2赋值C1自动生成INSERTINTOIDENT_TABLE(C2)VALUES(10);--查一下现在C1是多少SELECT*FROMIDENT_TABLE;--再插入第二行INSERTINTOIDENT_TABLE(C2)VALUES(20);--再查SELECT*FROMIDENT_TABLE;--查当前自增值SELECTident_current(SYSDBA.IDENT_TABLE)FROMDUAL;--结果应该是200因为最后一行C1是200--查种子和增量SELECTident_seed(SYSDBA.IDENT_TABLE)FROMDUAL;--100SELECTident_incr(SYSDBA.IDENT_TABLE)FROMDUAL;--100查看表空间使用情况其实就是一些用法记一下下面三个函数就足够。DM使用段、簇和页实现数据的物理组织。DM支持查看表的空间使用情况包括1.TABLE_USED_SPACE已分配给表的页面数2.TABLE_USED_PAGES表已使用的页面数3.TABLE_FREE_PAGE_STACK_USED_SPACE表空闲页堆栈拥有的页面数。