达梦数据库知识点
文章目录
- 达梦数据库知识点
- 测试数据
- 常用sql
- 表空间
- 表空间获取
- 创建删除
- 索引
- 全局索引
- 局部索引
- 执行计划操作符说明
- NSET：结果集收集
- PRJT：投影
- SLCT：选择
- AAGR：简单聚集
- FAGR：快速聚集
- HAGR：HASH 分组聚集
- SAGR：流分组聚集
- BLKUP：二次扫描 (回表)
- CSCN：全表扫描
- SSEK、CSEK、SSCN：索引扫描
- SSEK
- CSEK
- SSCN
- NEST LOOP：嵌套循环连接
- HASH JOIN：哈希连接
- MERGE JOIN：归并排序连接
- 统计信息更新sql
- 指定索引查询
测试数据
- 主表
-- SYSDBA.TABLE_CLASS_TEST definition
CREATE TABLE SYSDBA.TABLE_CLASS_TEST (
ID VARCHAR(100) NOT NULL,
NAME VARCHAR(100) NULL,
CODE VARCHAR(100) NULL,
TITLE VARCHAR(100) NULL,
CREATETIME TIMESTAMP NULL,
COLUMN1 VARCHAR(100) NULL,
COLUMN2 INTEGER NULL,
COLUMN3 VARCHAR(100) NULL,
COLUMN4 VARCHAR(300) NULL,
COLUMN5 VARCHAR(400) NULL,
COLUMN6 VARCHAR(100) NULL,
COLUMN7 VARCHAR(10) NULL,
CONSTRAINT TAVBLE_CLASS_TEST_PK PRIMARY KEY (ID)
);
CREATE UNIQUE INDEX INDEX33557764 ON SYSDBA.TABLE_CLASS_TEST (ID);- 子表
CREATE TABLE "SYSDBA"."TABLE_CLASS_TEST_CHILD"
(
"ID" VARCHAR(100) NOT NULL,
"NAME" VARCHAR(100),
"CODE" VARCHAR(100),
"TITLE" VARCHAR(100),
"CREATETIME" TIMESTAMP(6),
"COLUMN1" VARCHAR(100),
"COLUMN2" INTEGER,
"COLUMN3" VARCHAR(100),
"COLUMN4" VARCHAR(300),
CONSTRAINT "TABLE_CLASS_TEST_CHILD" NOT CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;常用sql
- 查看正在执行的语句
select * from v$sessions where state = 'ACTIVE';- 终止正在执行的语句
--sess_id是上面查询出的结果列
call sp_close_session(sess_id);表空间
- 表空间：表空间就如同创建了一个文件夹。创建一个单独的文件夹给你来专门放
DM资料，这样在查找时就可以只搜索这个文件夹，而不是整个硬盘搜索，从而提高效率。 - 达梦数据库默认使用
SYSDBA用户和默认表空间进行数据库操作。
表空间获取
select tablespace_name from dba_tablespaces;
* SYSTEM-系统表空间，存放数据字典信息
* ROLL-回滚表空间，存放的回滚数据，MVCC（事务多版本）
* TEMP-临时表空间，存放临时数据
* MAIN-main表空间，如果用户创建数据对象不指定存储位置，默认存放到main
* HMAIN-hmain表空间，存放huge table信息
创建删除
--创建表空间
CREATE TABLESPACE TEST DATAFILE 'TEST.DBF' SIZE 300;
--删除表空间
DROP TABLESPACE TEST;索引
全局索引
- 全局索引是以整张表的数据为对象而建立的索引。
- 指定
GLOBAL关键字创建的索引即为全局索引。创建全局索引时，会在水平分区表的主表上创建全局索引和在每个子表上创建全局本地索引。索引数据存储在全局索引上。全局本地索引的命名规则为：INDEX+ 全局本地索引ID_全局索引 ID。例如：全局索引IDX1的ID为3504，全局本地索引的ID为3506，那么全局本地索引的名称为INDEX3506_3504。 - 全局索引具体分为两种：全局非分区索引和全局分区索引。指定了
<PARTITION 子句 >的索引即为分区索引。只有达梦分布计算集群（DMDPC）功能支持分区索引。 - 全局非分区索引可用于除
DMDPC以外的任何环境中。
CREATE INDEX "TABLE_CLASS_TEST_INDEX1" ON "SYSDBA"."TABLE_CLASS_TEST" ("CREATETIME" DESC,"CODE" DESC)
GLOBAL STORAGE(ON "MAIN",CLUSTERBTR);局部索引
● 局部索引：局部索引是在分区表的每个分区上创建的索引。
- 未指定
GLOBAL关键字创建的索引即为局部索引。局部索引暂时不支持分区。创建局部索引时，会在水平分区表的主表上创建局部索引和在每个子表上创建子表局部索引。索引数据存储在子表局部索引上。 - 子表局部索引的命名规则为：
INDEX+ 子表局部索引ID_局部索引ID。例如：局部索引IDX1的ID为3504，子表局部索引的ID为3506，那么子表局部索引的名称为INDEX3506_3504。
CREATE INDEX TAVBLE_CLASS_TEST_CODE_IDX ON SYSDBA.TABLE_CLASS_TEST (CODE);执行计划操作符说明
- 在执行计划中命中顺序是左右边最上边优先执行，同一级上面的先执行
-
CSCN是CLUSTER INDEX SCAN的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。在一个高并发的系统中应尽量避免全表扫描。
NSET：结果集收集
CopyEXPLAIN SELECT * FROM T1;
#NSET2: [1, 10000, 156]
#PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)
#CSCN2: [1, 10000, 156]; INDEX33556710(T1)-
NSET是用于结果集收集的操作符，一般是查询计划的顶层节点，优化工作中无需对该操作符过多关注，一般没有优化空间。
PRJT：投影
CopyEXPLAIN SELECT * FROM T1;
#NSET2: [1, 10000, 156]
#PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)
#CSCN2: [1, 10000, 156]; INDEX33556710(T1)-
PRJT是关系的【投影】 (project) 运算，用于选择表达式项的计算。广泛用于查询，排序，函数索引创建等。优化工作中无需对该操作符过多关注，一般没有优化空间。
SLCT：选择
CopyEXPLAIN SELECT * FROM T1 WHERE C2='TEST';
#NSET2: [1, 250, 156]
#PRJT2: [1, 250, 156]; exp_num(5), is_atom(FALSE)
#SLCT2: [1, 250, 156]; T1.C2 = TEST
#CSCN2: [1, 10000, 156]; INDEX33556717(T1)-
SLCT是关系的【选择】运算，用于查询条件的过滤。可比较返回结果集与代价估算中是否接近，如相差较大可考虑收集统计信息。若该过滤条件过滤性较好，可考虑在条件列增加索引。
AAGR：简单聚集
CopyEXPLAIN SELECT COUNT(*) FROM T1 WHERE C1 = 10;
#NSET2: [0, 1, 4]
#PRJT2: [0, 1, 4]; exp_num(1), is_atom(FALSE)
#AAGR2: [0, 1, 4]; grp_num(0), sfun_num(1)
#SSEK2: [0, 1, 4]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]-
AAGR用于没有GROUP BY的COUNT、SUM、AGE、MAX、MIN等聚集函数的计算。
FAGR：快速聚集
CopyEXPLAIN SELECT MAX(C1) FROM T1;
#NSET2: [1, 1, 0]
#PRJT2: [1, 1, 0]; exp_num(1), is_atom(FALSE)
#FAGR2: [1, 1, 0]; sfun_num(1)-
FAGR用于没有过滤条件时，从表或索引快速获取MAX、MIN、COUNT值。
HAGR：HASH 分组聚集
CopyEXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C2;
#NSET2: [1, 100, 48]
#PRJT2: [1, 100, 48]; exp_num(1), is_atom(FALSE)
#HAGR2: [1, 100, 48]; grp_num(1), sfun_num(1)
#CSCN2: [1, 10000, 48]; INDEX33556717(T1)-
HAGR用于分组列没有索引只能走全表扫描的分组聚集，该示例中C2列没有创建索引。
SAGR：流分组聚集
CopyEXPLAIN SELECT COUNT(*) FROM T1 GROUP BY C1;
#NSET2: [1, 100, 4]
#PRJT2: [1, 100, 4]; exp_num(1), is_atom(FALSE)
#SAGR2: [1, 100, 4]; grp_num(1), sfun_num(1)
#SSCN: [1, 10000, 4]; IDX_C1_T1(T1)-
SAGR用于分组列是有序的情况下，可以使用流分组聚集，C1列上已经创建了索引，SAGR2性能优于HAGR2。
BLKUP：二次扫描 (回表)
CopyEXPLAIN SELECT * FROM T1 WHERE C1=10;
#NSET2: [0, 1, 156]
#PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)
#BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)
#SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]-
BLKUP先使用二级索引索引定位rowid，再根据表的主键、聚集索引、rowid等信息获取数据行中其它列。
CSCN：全表扫描
CopyEXPLAIN SELECT * FROM T1;
#NSET2: [1, 10000, 156]
#PRJT2: [1, 10000, 156]; exp_num(5), is_atom(FALSE)
#CSCN2: [1, 10000, 156]; INDEX33556710(T1)-
CSCN2是CLUSTER INDEX SCAN的缩写即通过聚集索引扫描全表，全表扫描是最简单的查询，如果没有选择谓词，或者没有索引可以利用，则系统一般只能做全表扫描。全表扫描I/O开销较大，在一个高并发的系统中应尽量避免全表扫描。
SSEK、CSEK、SSCN：索引扫描
Copy-- 创建所需索引
CREATE CLUSTER INDEX IDX_C1_T2 ON T2(C1);
CREATE INDEX IDX_C1_C2_T1 ON T1(C1,C2);SSEK
CopyEXPLAIN SELECT * FROM T1 WHERE C1=10;
#NSET2: [0, 1, 156]
#PRJT2: [0, 1, 156]; exp_num(5), is_atom(FALSE)
#BLKUP2: [0, 1, 156]; IDX_C1_T1(T1)
#SSEK2: [0, 1, 156]; scan_type(ASC), IDX_C1_T1(T1), scan_range[10,10]-
SSEK2是二级索引扫描即先扫描索引，再通过主键、聚集索引、rowid等信息去扫描表。
CSEK
CopyEXPLAIN SELECT * FROM T2 WHERE C1=10;
#NSET2: [0, 250, 156]
#PRJT2: [0, 250, 156]; exp_num(5), is_atom(FALSE)
#CSEK2: [0, 250, 156]; scan_type(ASC), IDX_C1_T2(T2), scan_range[10,10]-
CSEK2是聚集索引扫描只需要扫描索引，不需要扫描表，即无需BLKUP操作，如果BLKUP开销较大时，可考虑创建聚集索引。
SSCN
CopyEXPLAIN SELECT C1,C2 FROM T1;
#NSET2: [1, 10000, 60]
#PRJT2: [1, 10000, 60]; exp_num(3), is_atom(FALSE)
#SSCN: [1, 10000, 60]; IDX_C1_C2_T1(T1)-
SSCN是索引全扫描，不需要扫描表。
NEST LOOP：嵌套循环连接
- 嵌套循环连接是最基础的连接方式，将一张表（驱动表）的每一个值与另一张表（被驱动表）的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。驱动表的行数就是循环的次数，将在很大程度上影响执行效率。
- 连接列是否有索引，都可以走
NEST LOOP，但没有索引，执行效率会很差，语句如下所示：
Copyselect /*+use_nl(t1,t2)*/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';
#NSET2: [17862, 24725, 296]
#PRJT2: [17862, 24725, 296]; exp_num(8), is_atom(FALSE)
#SLCT2: [17862, 24725, 296]; T1.C1 = T2.C1
#NEST LOOP INNER JOIN2: [17862, 24725, 296];
#SLCT2: [1, 250, 148]; T1.C2 = 'A'
#CSCN2: [1, 10000, 148]; INDEX33555594(T1)
#CSCN2: [1, 10000, 148]; INDEX33555595(T2)可针对 T1 和 T2 的连接列创建索引，并收集统计信息，语句如下所示：
CopyCREATE INDEX IDX_T1_C2 ON T1(C2);
CREATE INDEX IDX_T2_C1 ON T2(C1);
DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T1_C2');
DBMS_STATS.GATHER_INDEX_STATS(USER,'IDX_T2_C1');再次查看执行计划可看出效率明显改善，代价有显著下降，语句如下所示：
Copyselect /*+use_nl(t1,t2)*/* from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';
#NSET2: [9805, 17151, 296]
#PRJT2: [9805, 17151, 296]; exp_num(8), is_atom(FALSE)
#SLCT2: [9805, 17151, 296]; T1.C1 = T2.C1
#NEST LOOP INNER JOIN2: [9805, 17151, 296];
#BLKUP2: [1, 175, 148]; IDX_T1_C2(T1)
#SSEK2: [1, 175, 148]; scan_type(ASC), IDX_T1_C2(T1), scan_range['A','A']
#CSCN2: [1, 10000, 148]; INDEX33555585(T2)适用场景：
- 驱动表有很好的过滤条件
- 表连接条件能使用索引
- 结果集比较小
HASH JOIN：哈希连接
哈希连接是在没有索引或索引无法使用情况下大多数连接的处理方式。哈希连接使用关联列去重后结果集较小的表做成 HASH 表，另一张表的连接列在 HASH 后向 HASH 表进行匹配，这种情况下匹配速度极快，主要开销在于对连接表的全表扫描以及 HASH 运算。
Copyselect * from t1 inner join t2 on t1.c1=t2.c1 where t1.c2='A';
1 #NSET2: [4, 24502, 296]
2 #PRJT2: [4, 24502, 296]; exp_num(8), is_atom(FALSE)
3 #HASH2 INNER JOIN: [4, 24502, 296]; KEY_NUM(1); KEY(T1.C1=T2.C1) KEY_NULL_EQU(0)
4 #SLCT2: [1, 250, 148]; T1.C2 = 'A'
5 #CSCN2: [1, 10000, 148]; INDEX33555599(T1)
6 #CSCN2: [1, 10000, 148]; INDEX33555600(T2)哈希连接比较消耗内存如果系统有很多这种连接时，需调整以下 3 个参数：
参数名 | 说明 |
HJ_BUF_GLOBAL_SIZE | HASH 连接操作符的数据总缓存大小 ()>=HJ_BUF_SIZE)，系统级参数，以兆为单位。有效值范围（10~500000） |
HJ_BUF_SIZE | 单个哈希连接操作符的数据总缓存大小，以兆为单位。有效值范围（2~100000） |
HJ_BLK_SIZE | 哈希连接操作符每次分配缓存( BLK )大小，以兆为单位，必须小于 HJ_BUF_SIZE。有效值范围（1~50） |
MERGE JOIN：归并排序连接
归并排序连接需要两张表的连接列都有索引，对两张表扫描索引后按照索引顺序进行归并。
Copy-- 对连接列创建索引
CREATE INDEX IDX_T1_C1C2 ON T1(C1,C2);
Copyselect /*+use_merge(t1 t2)*/t1.c1,t2.c1 from t1 inner join t2 on t1.c1=t2.c1 where t2.c2='b';
1 #NSET2: [13, 24725, 56]
2 #PRJT2: [13, 24725, 56]; exp_num(2), is_atom(FALSE)
3 #SLCT2: [13, 24725, 56]; T2.C2 = 'b'
4 #MERGE INNER JOIN3: [13, 24725, 56]; KEY_NUM(1); KEY(COL_0 = COL_0) KEY_NULL_EQU(0)
5 #SSCN: [1, 10000, 4]; IDX_C1_T1(T1)
6 #BLKUP2: [1, 10000, 52]; IDX_T2_C1(T2)
7 #SSCN: [1, 10000, 52]; IDX_T2_C1(T2)统计信息更新sql
DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TABLE_CLASS_TEST',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
# 或者
sp_index_stat_init('SYSDBA','TABLE_CLASS_TEST_ORDER_BY_INDEX1');指定索引查询
select /*+index(table_class_test TABLE_CLASS_TEST_ORDER_BY_INDEX1)*/
* from table_class_test where code <'10' order by createtime desc,code desc
--强制索引，/*.....*/第一个星星后不能有空格，里边内容结构为：加号index(表名 空格 索引名)。
--如果表用了别名，注释里的表也要使用别名。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
