1.统计信息

统计信息是存储在数据字典中的，用于描述 数据是如何在数据库中存储的，且从多个维度描述了数据库里对象的详细信息。统计信息是 优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。

dm收集统计信息的三种方法：

• 使用dbms_stats包收集
• 使用stat on语句收集
• 使用sp_stat函数收集

--dbms_stats 支持对模式下对象、单个表、单个索引收集统计信息，指定采样率
dbms_stats.gather_table_stats('DMTEST','T_TESTTAB',ESTIMATE_PERCENT=>100);
--查询sp_stat相关的函数
select name from v$ifun t where name like 'SP%_STAT%_INIT';
--stat 语句支持对表、索引、列收集统计信息
stat 100 on dmtest.t_testtab(userid);
复制

1.1 收集统计信息

1.1.1 收集数据库的统计信息

CALL SP_DB_STAT_INIT ();
复制

1.1.2 收集模式的统计信息

DBMS_STATS.GATHER_SCHEMA_STATS('模式名',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
复制

1.1.3 收集表的统计信息

CALL SP_TAB_STAT_INIT ('模式名', '表名');
DBMS_STATS.GATHER_TABLE_STATS('模式名','表名',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
复制

示例：

--收集表的统计信息，指定采样率为100%
dbms_stats.gather_table_stats('DMTEST','T_TESTTAB',ESTIMATE_PERCENT=>100);
复制

1.1.4 收集列的统计信息

1.1.4.1 收集所有列

--对某个表上所有的列生成统计信息
CALL SP_TAB_COL_STAT_INIT('模式名'，'表名')
--对某个表上所有的列，按照指定的采样率生成统计信息
CALL SP_STAT_ON_TABLE_COLS ('模式名','表名','采样率');
--兼容oracle语法
DBMS_STATS.GATHER_TABLE_STATS('模式名','表名',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');
复制

1.1.4.2 收集指定列

STAT <统计信息采样百分比> ON [<模式名>.]<表名>(<列名>);
CALL SP_COL_STAT_INIT ('模式名', '表名', '列名');
CALL SP_COL_STAT_INIT_EX(USER,'表名','列名',100);
复制

示例：

STAT 100 ON TEST(A);
复制

1.1.5 收集索引的统计信息

--收集某张表的所有索引的统计信息
CALL SP_TAB_INDEX_STAT_INIT('模式名', '表名');
--对指定的索引生成统计信息
CALL SP_INDEX_STAT_INIT('模式名', '索引名');
CALL SP_INDEX_STAT_INIT(USER,'索引名');
--兼容oracle语法
DBMS_STATS.GATHER_INDEX_STATS('模式名','索引名');

复制

1.1.6 自动收集统计信息

1.开启AUTO_STAT_OBJ,时间SP_CREATE_AUTO_STAT_TRIGGER

2.创建定时作业调用SP_DB_STAT_INT。

1.2 删除统计信息

DBMS_STATS.DELETE_TABLE_STATS
DBMS_STATS.DELETE_SCHEMA_STATS
DBMS_STATS.DELETE_INDEX_STATS
DBMS_STATS.DELETE_COLUMN_STATS('SYSDBA','T','NAME');

复制

1.3 查看统计信息

通过dbms_stats包查询：

DBMS_STATS.TABLE_STATS_SHOW
DBMS_STATS.INDEX_STATS_SHOW
DBMS_STATS.COLUMN_STATS_SHOW

复制

通过视图查询：

select * from SYSSTATS;
select * from USER_TAB_STATISTICS;
--从导出的统计信息表查询，stat$为前缀，T_STATTABLE为存放导出统计信息时创建的表
select * from "STAT$_T_STATTABLE";

复制

示例：

dbms_stats.table_stats_show('DMTEST','T_TESTTAB');
dbms_stats.column_stats_show('DMTEST','T_TESTTAB','USERID');

复制

1.4 导出导入统计信息

创建统计信息表：

--创建后，系统会增加一个stat$_T_STATTABLE表
dbms_stats.CREATE_STAT_TABLE('DMTEST','T_STATTABLE','TBS');

复制

导出统计信息：

--statid为标记，可选
dbms_stats.EXPORT_TABLE_stats('DMTEST','T_TESTTAB',
STATTAB=>'T_STATTABLE', statid=>'TEST01', statown=>'DMTEST');

复制

查看导出的统计信息：

select * from stat$_T_STATTABLE;

复制

1.5 直方图

1.5.1 使用场景

优化器会默认认为目标列的数据在其最小值（LOW_VALUE）和最大值（HIGH_VALUE）之间是 均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加 WHERE 查询条件后的可选择率以及结果集的 Cardinality，进而据此来计算成本值并选择执行计划。

但是，目标列的数据是均匀分布这个原则并不总是正确的，在实际的生产系统中，有很多表的列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均衡的。所以，此时 应该收集列的直方图。

直方图是一种列的特殊的统计信息，主要用来描述列上的数据分布情况。可以通过 sysstats 查看最大值和最小值：

SQL> desc sysstats;

行号     NAME              TYPE$          NULLABLE
---------- ----------------- -------------- --------
1          ID                INTEGER        N
2          COLID             SMALLINT       N
3          T_FLAG            CHAR(1)        N
4          T_TOTAL           BIGINT         N
5          N_SMAPLE          BIGINT         N
6          N_DISTINCT        BIGINT         N
7          N_NULL            BIGINT         N
8          V_MIN             VARBINARY(255) Y
9          V_MAX             VARBINARY(255) Y
10         BLEVEL            TINYINT        Y
11         N_LEAF_PAGES      BIGINT         Y
12         N_LEAF_USED_PAGES BIGINT         Y
13         CLUSTER_FACTOR    INTEGER        Y
14         N_BUCKETS         SMALLINT       Y
15         DATA              BLOB           Y
16         COL_AVG_LEN       INTEGER        Y
17         LAST_GATHERED     DATETIME(6)    Y
18         INFO1             VARBINARY(255) Y
19         INFO2             VARBINARY(255) Y

19 rows got

复制

1.5.2 直方图分类

1.5.2.1 频率直方图

根据列的值来规划桶数， 有几个不同的值 ， 那么就会有几个桶 。每个桶中记录访问的频率。达梦默认的情
况下为频率直方图。频率直方图收集的信息更准确。

验证默认为频率直方图：

1.创建表

drop table t1 purge;
create table t1 as select rownum no ,name from sysobjects where rownum<=2000;
select count (distinct no) from t1;

行号 COUNT(DISTINCTNO)
---------- -----------------
1 1876

复制

2.收集 size auto的直方图统计信息

DBMS_STATS.GATHER_TABLE_STATS('JT','T1',null,100,TRUE,'FOR ALL COLUMNS size auto');

复制

3.查看统计信息

SQL> DBMS_STATS.COLUMN_STATS_SHOW('JT','T1','NO');

行号     NUM_DISTINCT         LOW_VALUE HIGH_VALUE NUM_NULLS            NUM_BUCKETS SAMPLE_SIZE          HISTOGRAM
---------- -------------------- --------- ---------- -------------------- ----------- -------------------- ---------
1          1876                 1         1876       0                    1876        1876                 FREQUENCY
--查看第二个结果集
SQL> more 

复制

1.5.2.2 高度平衡直方图

当 distinct 的值超出桶数时为高度平衡的直方图， 将大量的数据分布到少数的桶中。每个桶的 Endpooint Number 相差越大，那么代表存放的越多。当收集直方图统计信息时，指定的method_opt 为 size auto 时，桶数可以足够多， 取决于有多少个 distinct 值，当手动指定size 值时，最多可以手动指定254。

高度平衡直方图，CBO会人为每个区间中的数据都是近似均匀分布的，实际上数据可能分布不均。

验证手工可以指定的最多桶数：

1.查询t1表数据分布

select count(distinct no) from t1;

行号 COUNT(DISTINCTNO)
---------- -----------------
1 1876

复制

2.收集统计信息

DBMS_STATS.GATHER_TABLE_STATS('JT','T1',null,100,TRUE,'FOR ALL COLUMNS size 254');

复制

3.查看直方图统计信息

SQL> DBMS_STATS.COLUMN_STATS_SHOW('JT','T1','NO');

行号     NUM_DISTINCT         LOW_VALUE HIGH_VALUE NUM_NULLS            NUM_BUCKETS SAMPLE_SIZE          HISTOGRAM      
---------- -------------------- --------- ---------- -------------------- ----------- -------------------- ---------------
1          1876                 1         1876       0                    251         1876                 HEIGHT BALANCED

复制

尝试收集 255个桶的直方图统计信息报错:

DBMS_STATS.GATHER_TABLE_STATS('JT','T1',null,100,TRUE,'FOR ALL COLUMNS size 255');

复制

不设置直方图的方式收集统计信息:

DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T',null,100,TRUE,'FOR ALL COLUMNS size 1');
DBMS_STATS.COLUMN_STATS_SHOW('JT','T1','NO');

复制

2.索引管理

DM8 提供了几种最常见类型的索引，对不同场景有不同的功能，它们是：

1. 聚集索引： 每一个普通表有且只有一个聚集索引，也叫聚簇索引；
2. 唯一索引：索引数据根据索引键唯一；
3. 函数索引：包含函数/表达式的预先计算的值；
4. 位图索引： 对低基数的列创建位图索引；
5. 位图连接索引： 针对两个或者多个表连接的位图索引，主要用于数据仓库中；
6. 全文索引：在表的文本列上建的索引；

说明：一级索引指聚簇索引，二级索引指B*tree索引，达梦的表是索引组织表，聚簇索引就是表

2.1 创建普通索引

普通索引：二级B*tree索引

--未创建索引走全表扫
explain select * from T_TESTTAB t where userid = 1;
--创建索引
create index ix_testtab_userid ON T_TESTTAB(userid) TABLESPACE TBSIDX online;
--查看执行计划，创建索引后，执行计划走二级索引定位查询
explain select * from T_TESTTAB t where userid = 1;
--更新数据，造成数据倾斜
update T_TESTTAB set userid=1 where userid < 90000;
commit;
--此时执行计划走索引定位
explain select * from T_TESTTAB t where userid = 1; 
--收集列的统计信息
stat 100 on dmtest.t_testtab(userid);
--查询执行计划，走全表扫
explain select * from T_TESTTAB t where userid = 1;

复制

说明：达梦创建索引未加online时，表上有insert事务未提交时，会产生等待，创建索引的ddl语句等待10s后会超时,加上online时会一直等待，事务提交后索引创建成功。

2.2 创建聚簇索引

--dm聚簇索引不能加online
create CLUSTER index ix_testtab_userid ON T_TESTTAB(userid);

复制

创建主键时是否指定为聚簇索引键由参数pk_with_cluster 指定。1 表示创建主键即为表的聚簇索引键。

SQL> select name,value from v$parameter where name='PK_WITH_CLUSTER';

行号     NAME            VALUE
---------- --------------- -----
1          PK_WITH_CLUSTER 0

复制

2.3 创建唯一索引

--创建的列上要唯一才可以创建唯一索引
create UNIQUE index ix_TESTTAB_username ON T_TESTTAB (username);

复制

2.4 创建位图索引

如果表上创建了聚簇索引，则不能在创建位图索引。位图用于列的值分布少，用于OLAP系统，查询较多，更新较少的情况。有位图索引的表，不适合经常更新。

create BITMAP index ix_testtab_optype ON T_TESTTAB(optype) TABLESPACE TBSIDX;

复制

2.5 创建函数索引

create index ix_TESTTAB_funusername ON T_TESTTAB (upper(username));

复制

2.6 创建分区索引

针对分区表有全局索引和局部索引，DM中分区表创建索引，默认创建的是局部索引，指定全局关键字global则为创建全局索引。oracle的索引默认为global。

分区表上创建local类型的索引：

--创建本地索引,索引数据存储在各个分区上
create index ix_PARTRANGETAB_username ON T_PARTRANGETAB(username);
create index ix_PARTRANGETAB_logtime ON T_PARTRANGETAB(logtime);
--查询索引信息
select t.INDEX_NAME, t.TABLE_NAME, t.INDEX_TYPE, t.PARTITIONED, t.GENERATED
from USER_INDEXES t
where t.TABLE_NAME='T_PARTRANGETAB' and t.INDEX_TYPE = 'NORMAL';
--查询索引分区信息
select * from USER_IND_PARTITIONS t where t.INDEX_NAME=upper('ix_PARTRANGETAB_username');
--查询索引段信息
select * from USER_SEGMENTS t where t.SEGMENT_NAME=upper('ix_PARTRANGETAB_username');

复制

创建全局索引：

create index ix_PARTRANGETAB_AREAID ON T_PARTRANGETAB(AREAID) global;
--查询索引信息
select t.INDEX_NAME, t.TABLE_NAME, t.INDEX_TYPE, t.PARTITIONED, t.GENERATED
from USER_INDEXES t
where t.TABLE_NAME like upper('T_PARTRANGE%')
and t.INDEX_TYPE = 'NORMAL';
--查询索引分区信息
select * from USER_IND_PARTITIONS t where t.INDEX_NAME like upper('ix_PARTRANGE%_username');
--查询索引段信息
select * from USER_SEGMENTS t where t.SEGMENT_NAME like 'IX_PARTRANGE%';

复制

3.维护索引

有效性/无效： 无效索引，DML操作不会维护索引数据，执行计划不会选择走该索引)无效的索引，重建后将有效。

可见性/不可见： 不可见索引，DML操作仍然会维护索引数据，但执行计划不会选择走该索引,不想让执行计划走这个索引，可以设置为不可见，不用删除索引。

重建索引：

SP_REBUILD_INDEX();
alter index ind_name rebuild;

复制

删除索引：

drop index ind_name;

复制

索引不可用：

alter index ix_TESTTAB_username UNUSABLE;
alter index ix_TESTTAB_username REBUILD ONLINE;

复制

索引不可见：

alter index ix_TESTTAB_username INVISIBLE;
alter index ix_TESTTAB_username VISIBLE;

复制

索引监控：

alter index ix_TESTTAB_username MONITORING USAGE;
alter index ix_TESTTAB_username NOMONITORING USAGE;
select * from SYS."V$OBJECT_USAGE";

复制

索引查询：

select * from user_indexes;
select index_name,table_name,status,visibility from user_indexes;
select * from sys.user_ind_columns;

复制

4.全文索引

全文检索技术是智能信息管理的关键技术之一，其主要目的就是实现对大容量的非结构化数据的快速查找。DM 实现了全文检索功能，并将其作为 DM 服务器的一个较独立的组件，提供更加准确的全文检索功能，较好地解决了模糊查询方式带来的问题。

4.1 全文检索概述

DM 全文检索根据已有词库建立全文索引，文本查询完全在索引上进行。全文索引为在字符串数据中进行复杂的词搜索提供了有效支持。用户可以在指定表的文本列上创建和删除全文索引。创建全文索引后全文索引未插入任何索引信息。当用户填充全文索引时，系统才将定义了全文索引的文本列的内容进行分词，并根据分词结果填充索引。用户可以在进行全文索引填充的列上使用 CONTAINS 谓词进行全文检索。

DM 全文索引改进了原有的分词算法，为全文检索提供了更好的基础。在创建全文索引成功后，假设索引名为 INDEX_NAME，则系统会自动产生如下相关的辅助表(I表,P 表,N 表,D 表)：

• CTI$INDEX_NAME$I：用于保存分词结果，记录词的基本信息，通过该信息可以快速地定位到该词的基表记录
• CTI$INDEX_NAME$P：保存基表发生的增量数据变化，用于全文索引的增量填充
• CTI$INDEX_NAME$N：用于保存原表记录 rowid 和新词条记录的 docid 的映射关系
• CTI$INDEX_NAME$D： 保存所有被删除的 docid，被删除的 docid 将不能通过全文索引查询

定义全文索引时可以指定分词参数 lexer，DM 包含 5 种分词参数：

• CHINESE_LEXER，中文最少分词；
• CHINESE_VGRAM_LEXER，机械双字分词；
• CHINESE_FP_LEXER，中文最多分词；
• ENGLISH_LEXER，英文分词；
• DEFAULT_LEXER，默认分词，为中文最少分词

4.2 创建全文索引

创建全文索引需要create context index权限，索引信息会保存在ctisys模式下的syscontextindexes系统表中。

创建全文索引： 手动填充

1.创建全文索引

--增加一个文本类型的列,并对这个列填充部分数据
alter table T_TESTTAB ADD COLUMN testinfo text;
--创建全文索引，不指定sync子句，后期需要手工填充
create CONTEXT index cti_TESTTAB_testinfo ON t_testtab(testinfo);
--使用contains关键字进行检索，没有填充查询不到数据
select * from t_testtab t where CONTAINS(testinfo, '链表');

复制

2.查询辅助表数据

select * from "CTI$CTI_TESTTAB_TESTINFO$D";
select * from "CTI$CTI_TESTTAB_TESTINFO$I";
select * from "CTI$CTI_TESTTAB_TESTINFO$P";
select * from "CTI$CTI_TESTTAB_TESTINFO$N";

复制

3.手动填充数据

--完全填充
alter CONTEXT index cti_TESTTAB_testinfo ON t_testtab REBUILD;
--增量填充
alter CONTEXT index cti_TESTTAB_testinfo ON t_testtab INCREMENT;

复制

创建全文索引： 自动填充

创建全文索引时，指定SYNC TRANSACTION子句填充，后期事务提交后自动填充

create table t_contexttab as select * from T_TESTTAB;
--分词关键字可以省略，创建后无需手动填充
create CONTEXT index cti_contexttab_testinfo ON t_contexttab(testinfo) LEXER default_lexer SYNC TRANSACTION;

复制

通过全文索引查询： 通过contains关键字

select * from t_contexttab t where CONTAINS(testinfo, '链表');
select * from t_contexttab t where CONTAINS(testinfo, '堆表' or 'ROWID');
select * from t_contexttab t where CONTAINS(testinfo, '堆表' and 'ROWID');

复制

4.3 全文索引的删除

--删除后使用contains 子句查询报错
drop CONTEXT index cti_contexttab_testinfo ON t_contexttab;

复制

4.4 全文索引相关视图

--记录全文索引所需要的词库信息
select * from SYSCONTEXTLIBS; 
select * from SYSCONTEXTINDEXES;

复制