达梦统计信息、Oracle统计信息、分区表

学习内容关键字：

达梦统计信息、Oracle统计信息、分区表

1. 达梦收集及查看统计信息方法

1.1 DBMS_STATS.GATHER_TABLE_STATS详解

作用:

DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息（默认参数下是对表进行直方图信息收集，包含该表的自身-表的行数、数据块数、行长等信息；列的分析--列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子）.

DBMS_STATS.GATHER_TABLE_STATS的语法如下:

DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);

参数说明:

ownname:要分析表的拥有者

tabname:要分析的表名.

partname:分区的名字,只对分区表或分区索引有用.

estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.

block_sapmple:是否用块采样代替行采样.

method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO):

for all columns:统计所有列的histograms.

for all indexed columns:统计所有indexed列的histograms.

for all hidden columns:统计你看不到列的histograms

for columns SIZE | REPEAT | AUTO | SKEWONLY:统计指定列的histograms

degree:决定并行度.默认值为null.

cascade:是收集索引的信息.默认为FALSE.

stattab:指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.

force:即使表锁住了也收集统计信息.

示例：

execute dbms_stats.gather_table_stats(ownname => 'owner',tabname => 'table_name' ,estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);

1.2 收集某列的信息

STAT 100 ON table_name(column_name);

STAT 100 ON OA_TEST.TEST01(ID);

1.3自动收集

--打开表数据量监控开关，参数值为 1 时监控所有表，2 时仅监控配置表

SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);

--设置 SYSDBA.T 表数据变化率超过 15% 时触发自动更新统计信息

DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T','STALE_PERCENT',15);

--配置自动收集统计信息触发时机

SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'14:36', '2020/3/31',60,1);

函数各参数介绍

SP_CREATE_AUTO_STAT_TRIGGER(

    TYPE                    INT,    --间隔类型，默认为天

    FREQ_INTERVAL         INT,    --间隔频率，默认 1

    FREQ_SUB_INTERVAL    INT,    --间隔频率，与 FREQ_INTERVAL 配合使用

    FREQ_MINUTE_INTERVAL INT,    --间隔分钟，默认为 1440

    STARTTIME              VARCHAR(128), --开始时间，默认为 22:00

    DURING_START_DATE    VARCHAR(128), --重复执行的起始时间，默认 1900/1/1

    MAX_RUN_DURATION    INT,    --允许的最长执行时间(秒)，默认不限制

    ENABLE                  INT     --0 关闭，1 启用  --默认为 1

);

1.4查看统计信息

示例:

select B.NAME as "表名",c.name as "列名",t_totle as "行数",n_sample as "采样行数"

   from sysstats a,(select id,name

       from sysobjects where schid in(select id from sysobjects where name='SYSDBA' and type$='SCH')) B,

    SYSCOLUMNS C

where A.ID = B.ID

    and A.ID = C.ID

    and A.COLID = C.COLID

    and B.NAME = 'SBTEST';

1.5更新统计信息

--更新已有统计信息

DBMS_STATS.UPDATE_ALL_STATS();

1.6删除统计信息

--表

DBMS_STATS.DELETE_TABLE_STATS('模式名','表名','分区名',...);

--模式

DBMS_STATS.DELETE_SCHMA_STATS('模式名','','',...);

--索引

DBMS_STATS.DELETE_INDEX_STATS('模式名','索引名','分区表名',...);

--字段

DBMS_STATS.DELETE_COLUMN_STATS('模式名','表名','列名','分区表名',...);

2.Oracle收集统计信息及查看方法

--收集表的统计信息

analyze table sbtest estimate statistics sample 100 percent for table;

--计算模式收集统计信息

analyze table sbtest computer statistics for table;

--收集列的统计信息

analyze table sbtest computer statistics for table for columns id,k,c,pad;

--删除统计信息

analyze table sbtest delete statistics;

--查看列统计信息

select column_name,table_name,last_analyzed from user_tab_col_statistics where table_name='SBTEST';

--查看表统计信息

select t.owner,t.table.t.column_name,t.mun_rows,t.sample_size,t.blocks,last_analyzed from dba_tables t where t.table_name='SBTEST';

--修改列约束

alter table SBTEST modify K INTERGER DEFAULT '' NOT NULL;

3. 分区表

概述：分区是指将表、索引等数据库对象划分为较小的可管理片段的技术，每一个片段称为分区子表或分区索引。一个表被分区后，对表的查询操作可以局限于某个分区进行，而不是整个表，这样可以大大提高查询速度。

DM 采用子表方式创建分区表，而且子表可以存储在不同的表空间上，这样做的好处有：

1. 减少所有数据都损坏的可能性，一个表空间损坏不影响其他表空间，提高可用性；
2. 恢复时间大大减少；
3. 可以将同一个表中的数据分布在不同的磁盘上，从而均衡磁盘上的 I/O操作；
4. 提高了表的可管理性、可利用性和访问效率。

在DM每一个分区以一个子表实体存在，即每一个分区都是一个完整的表，一般命名为主表名_分区名。对于水平分区，子表跟主表具有相同的逻辑结构，即分区子表与分区主表有相同的列定义和约束定义。在 DM 分区表中，主表本身不存储数据，所有数据只存储在子表中，从而实现不同分区的完全独立性。水平分区子表删除后，会将子表上的数据一起删除。

分区表维护

相关的数据字典： dba_tab_partitions;

Select * from dba_tab_partitions where table_owner=’TEST’;

1、增加分区

ALTER TABLE TEST.T1 add partition pn values less than(maxvalue);

2、删除分区

Alter table test.t1 drop partition pn;

注意；删除分区前请确认好，并做好备份。

3、合并分区

alter table test.t1 merge partitions p2,p3 into partition p2_3;

4、拆分分区

alter table test.t1 split partition P2_3 at(200) into (partition p2,

partition p3);

5、交换分区

ALTER TABLE TEST.T1 EXCHANGE PARTITION P2 WITH TABLE TEST.T10;

注意：交换时，会普通表的所有数据全部交换过来，包括不属于这个分区的数据也一起交换过来,所以我们在交换分区的时候，要把普通表的数据先整理好。

6、如何把非分区表转换成分区表

1）把非分区表的数据导出来。（可以采用达梦的DTS和DEXP工具进行数据导出）

2）建立一个和非分区表结构一样的分区表

3）将数据导入到分区表中

分区表创建索引

DM支持对水平分区表建立普通索引、唯一索引、聚集索引和函数索引。

分区表表的主键未包含所有分区列，系统会自动创建全局索引，否则自动创建局部索引。创建全局索引时，在水平分区表的主表创建全局索引，每个分区子表的数据都被索引在同一个B树中，并在每个子表创建全局本地索引，与主表共用一个B树。

当使用创建索引语句在水平分区表上创建索引时，指定GLOBAL关键字，创建全局索引，否则创建为局部索引。在水平分区表上创建唯一索引时，全局唯一索引总是可以创建的，若要创建非全局唯一索引，则要求索引键包含所有的分区键。这是因为对于局部索引，每一个分区子表都会建立一个索引分区，负责索引分区子表的数据。由于每个索引分区只负责索引本分区上的数据，其他分区上的数据无法维护，只有当分区键都包含在索引键中时，才能对分区主表保证索引键唯一。另外，能在水平分区表上创建局部聚集索引，不能创建全局聚集索引；不能在水平分区表上创建局部唯一函数索引。

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台