[转帖]金仓数据库KingbaseES分区表 -- 声明式创建分区表

数据库,kingbasees,分区表,声明,创建 · 浏览次数 : 0

小编点评

# 分区表不支持使用CONCURRENTLY在父表创建索引test=# create index CONCURRENTLY on tb1(info);ERROR: cannot create index on partitioned table \"public.tb1\" concurrentlyTime: 0.519 ms # 使用on only在分区表创建索引test=# create index on only tb1(info); CREATE INDEXTime: 1.845 ms # 查看分区表tb1信息，tb1_info_idx自动标记为有效 # 单独创建所有子分区索引test=# create index tb1_tb1_p1_info_idx on tb1_tb1_p1(info);CREATE INDEXtest=# create index tb1_tb1_p2_info_idx on tb1_tb1_p2(info);CREATE INDEXtest=# create index tb1_tb1_p3_info_idx on tb1_tb1_p3(info);CREATE INDEXtest=# create index tb1_tb1_p4_info_idx on tb1_tb1_p4(info);CREATE INDEXtest=# create index tb1_tb1_p5_info_idx on tb1_tb1_p5(info);CREATE INDEX# 使用attach partition将所有子分区索引附加到父表 #归纳总结以上内容，生成内容时需要带简单的排版

正文

https://www.modb.pro/db/638045

 

1. 创建分区表同时创建分区

1.1 准备环境

# 创建分区表同时创建分区
create table tb1(id bigint,stat date,no bigint,pdate date,info varchar2(50)) partition by range(pdate) INTERVAL ('1 MONTH'::INTERVAL)
(
PARTITION tb1_p1 VALUES LESS THAN ('2019-01-01'),
PARTITION tb1_p2 VALUES LESS THAN ('2019-02-01'),
PARTITION tb1_p3 VALUES LESS THAN ('2019-03-01'),
PARTITION tb1_p4 VALUES LESS THAN ('2019-04-01')
);

# 查看分区表父表
test=# \d+ tb1
                                        Partitioned table "public.tb1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Partitions: tb1_tb1_p1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb1_tb1_p2 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb1_tb1_p3 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb1_tb1_p4 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00')

# 查看分区表子表
test=# \d+ tb1_tb1_p1
                                          Table "public.tb1_tb1_p1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition of: tb1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00')
Partition constraint: ((pdate IS NOT NULL) AND ((pdate)::timestamp without time zone < '2019-01-01 00:00:00'::date))
Access method: heap

1.2 对分区表添加主键

# 分区表添加主键
test=# alter table tb1 add constraint tb1_pk primary key(id);
ALTER TABLE

# 查看分区表父表
test=# \d+ tb1
                                        Partitioned table "public.tb1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb1_pk" PRIMARY KEY, btree (id) INCLUDE (tableoid) GLOBAL 
Partitions: tb1_tb1_p1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb1_tb1_p2 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb1_tb1_p3 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb1_tb1_p4 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00')
            
# 查看分区表子表
test=# \d+ tb1_tb1_p1
                                          Table "public.tb1_tb1_p1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition of: tb1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00')
Partition constraint: ((pdate IS NOT NULL) AND ((pdate)::timestamp without time zone < '2019-01-01 00:00:00'::date))
Access method: heap

# 查询user_indexes视图tb1_pk索引
test=# select index_name,index_type,table_name,table_type,uniqueness,compression from user_indexes where index_name =upper('tb1_pk');
 index_name | index_type | table_name | table_type | uniqueness | compression 
------------+------------+------------+------------+------------+-------------
 TB1_PK     | BTREE      | TB1        | TABLE      | UNIQUE     | DISABLED
(1 row)

分区表添加主键：

  1. 添加主键的同时会创建主键列（字段）唯一索引（但是有唯一索引的列不一定是主键）。

  2. 主键字段不允许空值，添加主键过程中会自动添加not null非空约束，保证主键列值的唯一性。

  3. 分区表添加主键同时创建的索引（索引有GLOBAL）是全局索引。

  4. 分区表会在主键列创建一个全局（global）索引，默认为添加主键列的同时创建全局索引。

  5. 分区表唯一约束必须包含分区键。

1.3 对分区表创建索引

# 分区表创建索引
create index on tb1 (no) local;
CREATE INDEX
create index on tb1 (id,no) global;
CREATE INDEX

# 查看tb1表信息
test=# \d+ tb1
                                        Partitioned table "public.tb1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb1_pk" PRIMARY KEY, btree (id) INCLUDE (tableoid) GLOBAL 
    "tb1_id_no_idx" btree (id, no)
    "tb1_no_idx" btree (no)
Partitions: tb1_tb1_p1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb1_tb1_p2 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb1_tb1_p3 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb1_tb1_p4 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00')
            
# 查看分区表tb1子表信息
test=# \d+ tb1_tb1_p2
                                          Table "public.tb1_tb1_p2"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition of: tb1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00')
Partition constraint: (((pdate IS NULL) OR ((pdate)::timestamp without time zone >= '2019-01-01 00:00:00'::date)) AND ((pdate IS NOT NULL) AND ((pdate)::timestamp without time zone < '2019-02-01 00:00:00'::date)))
Indexes:
    "tb1_tb1_p2_id_no_idx" btree (id, no)
    "tb1_tb1_p2_no_idx" btree (no)
Access method: heap

分区表创建索引：

  1. 在分区表创建本地索引，会自动在每个分区上创建一个本地索引。

  2. 分区表只能在主键列创建一个全局（global）索引，默认为添加主键列创建的索引。

  3. 分区表创建全局索引必须满足条件：索引类型是唯一索引（unique）并且不包含分区键 。

  4. 分区表父表不支持CONCURRENTLY、parallel_workers选项，子分区支持CONCURRENTLY、parallel_workers选项。

1.4 使用ATTACH PARTITION将普通表转换为分区表子分区

# 创建普通表
test=# create table tb1_tb1_p5(id bigint,stat date,no bigint,pdate date,info varchar2(50));
CREATE TABLE

test=# \d+ tb1_tb1_p5
                                          Table "public.tb1_tb1_p5"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Access method: heap

# 使用ATTACH PARTITION将普通表转换为分区表子分区
test=# alter table tb1 ATTACH PARTITION tb1_tb1_p5 for VALUES FROM('2019-05-01') TO ('2019-05-31');
ERROR:  column "id" in child table must be marked NOT NULL

# 创建的普通表，表结构、约束必须跟分区表一致
test=# alter table tb1_tb1_p5 alter id set not null;
ALTER TABLE

test=# \d+ tb1_tb1_p5                               
                                          Table "public.tb1_tb1_p5"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Access method: heap

test=# alter table tb1 ATTACH PARTITION tb1_tb1_p5 for VALUES FROM('2019-05-01') TO ('2019-05-31');
ALTER TABLE

# 查看ATTACH后的分区表
test=# \d+ tb1
                                        Partitioned table "public.tb1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb1_pk" PRIMARY KEY, btree (id) INCLUDE (tableoid) GLOBAL 
    "tb1_id_no_idx" btree (id, no)
    "tb1_no_idx" btree (no)
Partitions: tb1_tb1_p1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb1_tb1_p2 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb1_tb1_p3 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb1_tb1_p4 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb1_tb1_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-05-31 00:00:00')
            
test=# \d+ tb1_tb1_p5
                                          Table "public.tb1_tb1_p5"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition of: tb1 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-05-31 00:00:00')
Partition constraint: (((pdate IS NULL) OR ((pdate)::timestamp without time zone >= '2019-05-01 00:00:00'::date)) AND ((pdate IS NOT NULL) AND ((pdate)::timestamp without time zone < '2019-05-31 00:00:00'::date)))
Indexes:
    "tb1_tb1_p5_id_no_idx" btree (id, no)
    "tb1_tb1_p5_no_idx" btree (no)
Access method: heap

ATTACH PARTITION将普通表转换为分区表子分区：

1 ATTACH普通表、分区表的列、字段类型、长度、约束必须一致。

2 分区表的unique和primary key约束将被应用在ATTACH新的子分区。

3 ATTACH过程中如果普通表有数据，会使用全表扫描检查数据是否违反分区约束（可以在ATTACH前使用约束筛选复合条件的数据）。

4 ATTACH外部表，不需要验证外部表中的数据符合分区约束。

5 如果ATTACH的表有跟分区不一致的索引，分区表会应用ATTACH表的索引。

2. 使用Create Table为分区表添加子分区

使用Create Table语句创建分区表子分区也会自动添加约束及索引。

# 使用Create Table语句创建分区表子分区
test=# CREATE TABLE tb1_tb1_p5 PARTITION OF tb1 FOR VALUES FROM ('2019-04-01') TO ('2019-04-30');
CREATE TABLE

test=# \d+ tb1
                                        Partitioned table "public.tb1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb1_pk" PRIMARY KEY, btree (id) INCLUDE (tableoid) GLOBAL 
    "tb1_id_no_idx" btree (id, no)
    "tb1_no_idx" btree (no)
Partitions: tb1_tb1_p1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb1_tb1_p2 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb1_tb1_p3 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb1_tb1_p4 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb1_tb1_p5 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-04-30 00:00:00')

test=# \d+ tb1_tb1_p5
                                          Table "public.tb1_tb1_p5"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition of: tb1 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-04-30 00:00:00')
Partition constraint: (((pdate IS NULL) OR ((pdate)::timestamp without time zone >= '2019-04-01 00:00:00'::date)) AND ((pdate IS NOT NULL) AND ((pdate)::timestamp without time zone < '2019-04-30 00:00:00'::date)))
Indexes:
    "tb1_tb1_p5_id_no_idx" btree (id, no)
    "tb1_tb1_p5_no_idx" btree (no)
Access method: heap

3. 申明式创建分区总结

  1. 声明式分区，子分区和分区表列、类型、约束必须一致。

  2. 在申明式创建的分区表上创建索引，会自动将索引应用于所有的子分区。

  3. 分区表惟一约束必须包括分区键。

  4. 不能创建包含所有子分区的排除约束，只能每个子分区单独创建。

  5. 在分区表创建索引时（不可使用CONCURRENTLY），可使用on only在分区表创建标记失效的索引，避免大表创建索引耗时太久（子分区不会自动应用该索引），然后在所有子分区单独创建索引（可使用CONCURRENTLY），最后使用ALTER INDEX … ATTACH PARTITION附加到到父索引，所有子分区索引附加到父索引后会自动标记为有效。

# 分区表不支持使用CONCURRENTLY在父表创建索引
test=# create index CONCURRENTLY on tb1(info);
ERROR:  cannot create index on partitioned table "tb1" concurrently
Time: 0.519 ms

# 使用on only在分区表创建索引
test=# create index on only tb1(info);    
CREATE INDEX
Time: 1.845 ms

# 查看分区表tb1信息，tb1_info_idx标记为无效invalid
test=# \d+ tb1
                                        Partitioned table "public.tb1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb1_pk" PRIMARY KEY, btree (id) INCLUDE (tableoid) GLOBAL 
    "tb1_id_no_idx" btree (id, no)
    "tb1_info_idx" btree (info) INVALID
    "tb1_no_idx" btree (no)
Partitions: tb1_tb1_p1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb1_tb1_p2 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb1_tb1_p3 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb1_tb1_p4 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb1_tb1_p5 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-04-30 00:00:00')

# 单独创建所有子分区索引
test=# create index tb1_tb1_p1_info_idx on tb1_tb1_p1(info);
CREATE INDEX
test=# create index tb1_tb1_p2_info_idx on tb1_tb1_p2(info);
CREATE INDEX
test=# create index tb1_tb1_p3_info_idx on tb1_tb1_p3(info);
CREATE INDEX
test=# create index tb1_tb1_p4_info_idx on tb1_tb1_p4(info);
CREATE INDEX
test=# create index tb1_tb1_p5_info_idx on tb1_tb1_p5(info);
CREATE INDEX

# 使用attach partition将所有子分区索引附加到父表
test=# alter index tb1_info_idx attach partition tb1_tb1_p1_info_idx;
ALTER INDEX
test=# alter index tb1_info_idx attach partition tb1_tb1_p2_info_idx;
ALTER INDEX
test=# alter index tb1_info_idx attach partition tb1_tb1_p3_info_idx;
ALTER INDEX
test=# alter index tb1_info_idx attach partition tb1_tb1_p4_info_idx;
ALTER INDEX
test=# alter index tb1_info_idx attach partition tb1_tb1_p5_info_idx;
ALTER INDEX

# 查看分区表tb1信息，tb1_info_idx自动标记为有效
test=# \d+ tb1
                                        Partitioned table "public.tb1"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb1_pk" PRIMARY KEY, btree (id) INCLUDE (tableoid) GLOBAL 
    "tb1_id_no_idx" btree (id, no)
    "tb1_info_idx" btree (info)
    "tb1_no_idx" btree (no)
Partitions: tb1_tb1_p1 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb1_tb1_p2 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb1_tb1_p3 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb1_tb1_p4 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb1_tb1_p5 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-04-30 00:00:00')
数据库 kingbasees 分区表 声明 创建

与[转帖]金仓数据库KingbaseES分区表 -- 声明式创建分区表相似的内容：

[转帖]金仓数据库KingbaseES分区表 -- 声明式创建分区表

https://www.modb.pro/db/638045 1. 创建分区表同时创建分区 1.1 准备环境 # 创建分区表同时创建分区 create table tb1(id bigint,stat date,no bigint,pdate date,info varchar2(50)) part

数据库 kingbasees 分区表 声明
0
[转帖]金仓数据库KingbaseES表空间介绍

1、表空间的概念 KingbaseES中的表空间允许在文件系统中定义用来存放表示数据库对象的文件的位置。在KingbaseES中表空间实际上就是给表指定一个存储目录。 2、表空间的作用 通过使用表空间，管理员可以控制一个KingbaseES安装的磁盘布局。 如果初始化集簇所在的分区或者卷用光了空间，

数据库 kingbasees 空间 介绍
0
[转帖]炫“库”行动—人大金仓有奖征文——金仓分析型数据库系统执行计划生成和查看

【本文正在参与炫“库”行动—人大金仓有奖征文】 人大金仓有奖征文 (csdn.net)https://bss.csdn.net/m/topic/kingbase 一、执行计划生成 EXPLAIN和EXPLAIN ANALYZE是金仓分析型数据库系统优化性能的工具。EXPLAIN会为查询显示其查询计划

行动 人大 有奖 征文
0
[转帖]金仓数据库KingbaseES 数据库参数优化

目录 一、数据库应用类型 二、主要参数 max_connections shared_buffers effective_cache_size maintenance_work_mem checkpoint_completion_target wal_buffers default_statisti

数据库 kingbasees 参数 优化
0
[转帖]金仓数据库KingbaseES V8R6 中unlogged表

KingbaseESV8R6有一种表称为unlogged，在该表新建的索引也属于unlogged。和普通表的区别是，对该表进行DML操作时候不将该表的变更记录变更写入到wal文件中。在数据库异常关机或者异常崩溃后该表的数据会被truncate掉，但是在写入性能上会比普通表快几倍。 这个特性类似于or

数据库 kingbasees v8r6 unlogged
0
[转帖]金仓数据库KingbaseES误删除系统超级用户（superuser）权限的恢复方式

`https://blog.csdn.net/arthemis_14/article/details/129879269` 在使用KingbaseES数据库的时候，系统默认存在一个跟系统初始化用户同名的Superuser（默认是system用户，可更改）。 这个Superuser的存在其实对于权限的

数据库 kingbasees 误删除 系统
0
[转帖]金仓数据库KingbaseES V8R6 索引膨胀

索引膨胀 对于索引，随着业务不断的增删改，会造成膨胀，尤其Btree索引，也会涉及索引分裂、合并等，导致索引访问效率降低、维护成本增加。另外，索引页的复用与HEAP PAGE不一样，因为索引的内容是有序结构，只有符合顺序的ITEM才能插入对应的PAGE中，不像HEAP TUPLE，只要有空间就可以插

数据库 kingbasees v8r6 索引
0
[转帖]金仓数据库KingbaseES数据目录结构

KingbaseES数据库结构 [kingbase@postgresV8]$ tree -LP2data/.├── data│ ├── base # 存储用户创建的数据库文件及隶属于用户数据库的所有关系.比如表、索引...│ ├── current_logfiles. # 记录当前被日志收集器写入的

数据库 kingbasees 数据 目录
0
[转帖]金仓数据库KingbaseES V8R6索引坏块故障处理

案例说明： 在执行表数据查询时，出现下图所示错误，索引故障导致表无法访问，后重建索引问题解决。本案例复现了此类故障解决过程。 适用版本： KingbaseES V8R3/R6 一、创建测试环境 # 表结构信息prod=# \d+ test1 Table "public.test1" Column|

数据库 kingbasees v8r6 索引
0
[转帖]金仓数据库KingbaseES表空间(tablespace)知多少

金仓数据库KingbaseES表空间定义 金仓数据库KingbaseES中的表空间允许在文件系统里定义那些代表数据库对象的文件存放位置，比如表和索引等。一旦表空间被创建，那么就可以在创建数据库对象时通过名称来引用他。 一个数据库可以有一个或多个表空间，创建数据库时自动创建系统表空间sys_defau

数据库 kingbasees 空间 tablespace
0
# 热门排行
微软 New Bing AI 申请与使用保姆级教程（免魔法） ChatGPT API使用介绍 ChatGPT开发实战 一篇带你了解如何使用纯前端类Excel表格构建现金流量表 手把手教你玩转 Excel 数据透视表 为什么 C# 可能是最好的第一编程语言 .NET 入门到高级路线 提高工作效率的神器：基于前端表格实现Chrome Excel扩展插件 React + Springboot + Quartz，从0实现Excel报表自动化 用Echarts实现前端表格引用从属关系可视化
© PmDaddy. 2023 PmDaddy教程网 All rights reserved.