KingbaseES 分区表修改字段类型

news/2024/12/30 3:45:16/

KingbaseES普通表修改表结构请参考：KingbaseES变更表结构表重写问题

数据类型转换重写与不重写：

  • varchar(x) 转换到 varchar(y) 当 y>=x，不需要重写。
  • numeric(x,z) 转换到 numeric(y,z) 当 y>=x，或者不指定精度类型，不需要重写。
  • numeric(x,c) 转换到 numeric(y,z) 当 y=x c>z，当numeric数据类型标度不一致时，需要重写。
  • varbit(x) 转换到 varbit(y) 当 y>=x，不需要重写。
  • timestamp(x) 转换到 timestamp(y) 当 y>=x，或者转换为timestamp，不需要重写。
  • timestamptz(x) 转换到 timestamptz(y) 当 y>=x，或者转换为timestamptz，不需要重写。
  • interval(x) 转换到 interval(y) 当 y>=x ，或者转换为interval，不需要重写。
  • timestamp 转换到 text、varchar、varchar(n)，char(n)，需要重写。
  • timestamp(x)转换到 text、varchar、varchar(n)、char(n)，n>=x，需要重写。
  • text 转换到 char、char(x)、varchar(n)，需要重写。
  • text 转换到 varchar，不需要重写。
  • numeric(x) 转换到 numeric(y)，y>=x，不需要重写。
  • numeric(x) 转换到 numeric，不需要重写。
  • numeric(x,y) 转换到 numeric，不需要重写。

一、普通表的修改：

普通表数据类型长度或者精度由小改大表不会重写，索引也不会重写。

test=# create table t01(id int,name varchar(10));
CREATE TABLE
test=# insert into t01 select generate_series(1,10),substr(md5(random()::text),1,10);
INSERT 0 10
test=# create index on t01 (name);
CREATE INDEX
test=# \d t01Table "public.t01"Column |            Type            | Collation | Nullable | Default 
--------+----------------------------+-----------+----------+---------id     | integer                    |           |          | name   | character varying(10 char) |           |          | 
Indexes:"t01_name_idx" btree (name)test=# 
test=# select sys_relation_filenode('t01'),sys_relation_filenode('t01_name_idx');SYS_RELATION_FILENODE | SYS_RELATION_FILENODE 
-----------------------+-----------------------289043 |                289046
(1 row)# 设置客户端消息级别client_min_messages=debug5
test=# set client_min_messages=debug5;# 修改t01表name字段长度varchar(10)为varchar(15)
test=# alter table t01 alter column name type varchar(15);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  rehashing catalog cache id 68 for sys_recyclebin; 33 tups, 16 buckets
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428092/1/7
ALTER TABLEtest=# select sys_relation_filenode('t01'),sys_relation_filenode('t01_name_idx');
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0SYS_RELATION_FILENODE | SYS_RELATION_FILENODE 
-----------------------+-----------------------289043 |                289046
(1 row)test=#

分区表修改表结构是否跟普通表一样？

二、分区表的修改：

分区表分区键无法修改数据类型，本测试只针对非分区键进行测试。

create table tb(id bigint,pdate date,info varchar2(10)) partition by range(pdate) INTERVAL ('1 MONTH'::INTERVAL)
(
PARTITION tb_p0 VALUES LESS THAN ('2023-01-01'),
PARTITION tb_p1 VALUES LESS THAN ('2023-02-01'),
PARTITION tb_p2 VALUES LESS THAN ('2023-03-01'),
PARTITION tb_p3 VALUES LESS THAN ('2023-04-01')
);
CREATE TABLE
insert into tb select generate_series(1,100),'2023-01-01'::date,substr(md5(random()::text),1,10);
INSERT 0 100
insert into tb select generate_series(101,200),'2023-02-01'::date,substr(md5(random()::text),1,10);
INSERT 0 100
insert into tb select generate_series(201,300),'2023-03-01'::date,substr(md5(random()::text),1,10);
INSERT 0 100

2.1.分区表非索引列数据类型的修改：

修改非索引列的类型：由小改大，表不会发生重写，索引自然也没有发生重写。

修改非索引列的类型：由大改小，遵循KingbaseES变更表结构表重写规则，表需要重写，索引也需要重写。

test=# set client_min_messages =debug5;
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SET
test=# select oid,relname from sys_class where relname='tb';      
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME 
--------+---------289063 | tb
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p0';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289066 | tb_tb_p0
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p1';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289069 | tb_tb_p1
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p2';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289072 | tb_tb_p2
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p3';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289075 | tb_tb_p3
(1 row)# 修改tb分区表info列为varchar(20)
test=# alter table tb alter column info type varchar(20);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428111/1/10
ALTER TABLEtest=# select oid,relname from sys_class where relname='tb';      
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME 
--------+---------289063 | tb
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p0';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289066 | tb_tb_p0
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p1';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289069 | tb_tb_p1
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p2';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289072 | tb_tb_p2
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p3';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289075 | tb_tb_p3
(1 row)test=# alter table tb alter column info type varchar(30);                 
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428112/1/10
ALTER TABLE
test=# select oid,relname from sys_class where relname='tb';      
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME 
--------+---------289063 | tb
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p0';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289066 | tb_tb_p0
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p1';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289069 | tb_tb_p1
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p2';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289072 | tb_tb_p2
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p3';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289075 | tb_tb_p3
(1 row)

分区表修改非索引列字段长度或者是精度由小变大，跟普通表一样，不需要重写。

2.2.分区表全局(GLOBAL)索引列数据类型的修改：

修改全局(GLOBAL)索引列的类型：由小改大，表不会发生重写，索引也没有发生重写。

修改全局(GLOBAL)索引列的类型：由大改小，遵循KingbaseES变更表结构表重写规则，表需要重写，索引也需要重写。

test=# \d+ tb                          Partitioned table "public.tb"Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------id     | bigint                     |           |          |         | plain    |              | pdate  | date                       |           |          |         | plain    |              | info   | character varying(10 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')# 添加主键
test=# alter table tb add constraint tb_pk primary key (info);
ALTER TABLE
test=# \d+ tbPartitioned table "public.tb"Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------id     | bigint                     |           |          |         | plain    |              | pdate  | date                       |           |          |         | plain    |              | info   | character varying(10 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:"tb_pk" PRIMARY KEY, btree (info) INCLUDE (tableoid) GLOBAL 
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')test=# select sys_relation_filepath('tb_pk');                     SYS_RELATION_FILEPATH 
-----------------------base/12176/289111
(1 row)test=# select oid,relname from sys_class where relname='tb';      OID   | RELNAME 
--------+---------289063 | tb
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p0';OID   | RELNAME  
--------+----------289066 | tb_tb_p0
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p1';OID   | RELNAME  
--------+----------289069 | tb_tb_p1
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p2';OID   | RELNAME  
--------+----------289072 | tb_tb_p2
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p3';OID   | RELNAME  
--------+----------289075 | tb_tb_p3
(1 row)test=# alter table tb alter info type varchar(20);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  drop auto-cascades to index tb_pk
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428121/1/19
ALTER TABLE
test=# select sys_relation_filepath('tb_pk');                     
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0SYS_RELATION_FILEPATH 
-----------------------base/12176/289111
(1 row)test=# select oid,relname from sys_class where relname='tb';      
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME 
--------+---------289063 | tb
(1 row)test=# select oid,relname from sys_class where relname='tb_tb_p2';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   | RELNAME  
--------+----------289072 | tb_tb_p2
(1 row)# 添加全局索引
test=# create unique index on tb(id,info) global;
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  building index "tb_id_info_idx" on table "tb" serially
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428122/1/4
CREATE INDEX
test=# \d+ tbPartitioned table "public.tb"Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------id     | bigint                     |           |          |         | plain    |              | pdate  | date                       |           |          |         | plain    |              | info   | character varying(20 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:"tb_pk" PRIMARY KEY, btree (info) INCLUDE (tableoid) GLOBAL "tb_id_info_idx" UNIQUE, btree (id, info) INCLUDE (tableoid) GLOBAL 
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')test=# select sys_relation_filepath('tb_pk');                     
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0SYS_RELATION_FILEPATH 
-----------------------base/12176/289111
(1 row)test=# select sys_relation_filepath('tb_id_info_idx');
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0SYS_RELATION_FILEPATH 
-----------------------base/12176/289115
(1 row)test=# alter table tb alter info type varchar(30);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  drop auto-cascades to index tb_pk
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428123/1/27
ALTER TABLE
test=# select sys_relation_filepath('tb_pk');                     
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0SYS_RELATION_FILEPATH 
-----------------------base/12176/289111
(1 row)test=# select sys_relation_filepath('tb_id_info_idx');
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0SYS_RELATION_FILEPATH 
-----------------------base/12176/289115
(1 row)

分区表修改全局(GLOBAL)索引列字段长度或者是精度由小变大，跟普通表一样，不需要重写。

2.3.分区表本地(LOCAL)索引列数据类型的修改：

修改本地(LOCAL)索引列的类型：由小改大，表不会发生重写，索引发生重写。

修改本地(LOCAL)索引列的类型：由大改小，遵循KingbaseES变更表结构表重写规则，表需要重写，索引也需要重写。

test=# \d+ tbPartitioned table "public.tb"Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------id     | bigint                     |           |          |         | plain    |              | pdate  | date                       |           |          |         | plain    |              | info   | character varying(30 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:"tb_pk" PRIMARY KEY, btree (info) INCLUDE (tableoid) GLOBAL "tb_id_info_idx" UNIQUE, btree (id, info) INCLUDE (tableoid) GLOBAL 
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')# 创建本地索引
test=# create index on tb(pdate,info);        
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  building index "tb_tb_p0_pdate_info_idx" on table "tb_tb_p0" serially
DEBUG:  building index "tb_tb_p1_pdate_info_idx" on table "tb_tb_p1" serially
DEBUG:  building index "tb_tb_p2_pdate_info_idx" on table "tb_tb_p2" serially
DEBUG:  building index "tb_tb_p3_pdate_info_idx" on table "tb_tb_p3" serially
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428125/1/12
CREATE INDEX
test=# \d+ tbPartitioned table "public.tb"Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------id     | bigint                     |           |          |         | plain    |              | pdate  | date                       |           |          |         | plain    |              | info   | character varying(30 char) |           | not null |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:"tb_pk" PRIMARY KEY, btree (info) INCLUDE (tableoid) GLOBAL "tb_id_info_idx" UNIQUE, btree (id, info) INCLUDE (tableoid) GLOBAL "tb_pdate_info_idx" btree (pdate, info)
Partitions: tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2023-01-01 00:00:00'),tb_tb_p1 FOR VALUES FROM ('2023-01-01 00:00:00') TO ('2023-02-01 00:00:00'),tb_tb_p2 FOR VALUES FROM ('2023-02-01 00:00:00') TO ('2023-03-01 00:00:00'),tb_tb_p3 FOR VALUES FROM ('2023-03-01 00:00:00') TO ('2023-04-01 00:00:00')test=# select sys_relation_filepath('tb_pk'),sys_relation_filepath('tb_id_info_idx');
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0SYS_RELATION_FILEPATH | SYS_RELATION_FILEPATH 
-----------------------+-----------------------base/12176/289111     | base/12176/289115
(1 row)test=# select oid,relname from sys_class where relname='tb_pdate_info_idx';
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   |      RELNAME      
--------+-------------------289119 | tb_pdate_info_idx
(1 row)# 修改本地索引依赖info列
test=# alter table tb alter info type varchar(40);
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  EventTriggerInvoke 267860
DEBUG:  drop auto-cascades to index tb_tb_p0_pdate_info_idx
DEBUG:  drop auto-cascades to index tb_tb_p1_pdate_info_idx
DEBUG:  drop auto-cascades to index tb_tb_p2_pdate_info_idx
DEBUG:  rehashing catalog cache id 68 for sys_recyclebin; 33 tups, 16 buckets
DEBUG:  drop auto-cascades to index tb_tb_p3_pdate_info_idx
DEBUG:  drop auto-cascades to index tb_pk
DEBUG:  building index "tb_tb_p0_pdate_info_idx" on table "tb_tb_p0" serially
DEBUG:  building index "tb_tb_p1_pdate_info_idx" on table "tb_tb_p1" serially
DEBUG:  building index "tb_tb_p2_pdate_info_idx" on table "tb_tb_p2" serially
DEBUG:  building index "tb_tb_p3_pdate_info_idx" on table "tb_tb_p3" serially
DEBUG:  EventTriggerInvoke 13761
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 428127/1/51
ALTER TABLE
test=# select sys_relation_filepath('tb_pk'),sys_relation_filepath('tb_id_info_idx');
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0SYS_RELATION_FILEPATH | SYS_RELATION_FILEPATH 
-----------------------+-----------------------base/12176/289111     | base/12176/289115
(1 row)test=# select oid,relname from sys_class where relname='tb_pdate_info_idx';          
DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0OID   |      RELNAME      
--------+-------------------289127 | tb_pdate_info_idx
(1 row)test=#

分区表本地(LOCAL)索引列修改字段长度或者是精度由小变大，表、全局索引不需要重写，但是本地索引需要重写。

2.4.分区表修改表结构总结：

  • 分区表修改非索引列字段长度或者是精度由小变大，跟普通表一样，不需要重写。

  • 分区表修改全局(GLOBAL)索引列字段长度或者是精度由小变大，跟普通表一样，不需要重写。

  • 分区表本地(LOCAL)索引列修改字段长度或者是精度由小变大，表、全局索引不需要重写，但是本地索引需要重写。

三、分区表变更表结构的思考：

通过以上测试可知：分区表修改本地索引依赖字段长度或者标度会导致索引重写。

如果分区表数据量大、子分区多、字段列依赖的索引多，修改分区表(本地索引)列字段长度会发生如下问题：

修改字段长度由小到大：分区表所有的子表不会发生重写，但是索引会发生重写，索引越多阻塞时间越长。

目前想到的解决方法：删除被修改列依赖的所有本地索引，避免长时间的AccessExclusiveLock，修改完成后子表使用concurrently的方式创建。

修改字段长度由大到小：分区表所有的子表都会发生重写，只要表发生重写所有的索引都需要重写，此过程会导致业务不可用(影响太大)。

对分区表使用detach？分区表要求所有子分区的表结构必须跟父表一致，所有的子分区全部修改完成后再attach回去，跟直接改区别不大，可以避免业务中断。需要根据业务提前准备好脚本。


http://www.ppmy.cn/news/65948.html

相关文章

Hacking The Box----Awkward

Hacking The Box----Awkward

信息收集 nmap扫描，发现22号端口和80号端口打开，80号端口上运行着http服务器。访问ip后URL变为hat-valley.htb 修改/etc/hosts文件，添加10.10.11.185 hat-valley.htb，然后就能正常访问网站。可以看到是一家卖帽子的公司的网站&a…
阅读更多...
品类超全的免费 API 大全整理

品类超全的免费 API 大全整理

AI 绘画：通过AI 生成图片，包括图生文、文生图等。人脸检测：快速检测图片中的人脸并返回人脸位置，输出人脸关键点坐标，支持识别多张人脸。静态活体检测：静态活体检测主要用于针对用户上传图像，返…
阅读更多...
如何将ChatGPT训练成某个领域的专家！附完整示例！

如何将ChatGPT训练成某个领域的专家！附完整示例！

背景 最近听了 八叉的一个ChatGPT讲座，讲的是如何将ChatGPT训练成领域专家，这样我们就可以用它来解决该领域的各种问题。 整个讲座中最让我印象深刻的就是训练方法，它是通过让ChatGPT向我们提问，然后由我们给出答案的方式进行训…
阅读更多...
SpringCloud最流行的39道面试题含答案

SpringCloud最流行的39道面试题含答案

目录 官方文档地址1. 什么是 Spring Cloud?2. Spring Cloud 的主要组件是什么？3. 如何配置 Eureka?4. 什么是 Hystrix?5. 如何编写一个基于 zuul 的微服务？6. 什么是 Ribbon?7. 如何编写一个基于 Ribbon 的微服务？8. 什么是 Eureka 客户端…
阅读更多...
重要通知｜Sui测试网将于5月11日重置

重要通知｜Sui测试网将于5月11日重置

致Sui社区成员们： 正如之前公告所述，部分社区成员发现测试网可能会定期清除数据并重新启动。鉴于此，我们宣布计划将于2023年5月11日进行首次测试网清除。 对于想要继续读取和访问当前测试网络的社区成员，请使用由Mysten Labs在…
阅读更多...
GB/T25915.1法规基本标准-附 录 B(资料性)等级划分计算

GB/T25915.1法规基本标准-附 录 B(资料性)等级划分计算

附 录 B(资料性)等级划分计算实例 B.1 示例1 B.1.1 某个洁净室占地面积18m2,规定洁净度级别为动态ISO5级。使用采样流量为28.3L/min的 离散粒子计数器进行分级测试。2个关注粒径分别为:D≥0.3μm 和D≥0.5μm。 查表 A.1,采样点数 NL 为6。 B.1.2 从表1查得ISO5级的粒子浓度…
阅读更多...
从爆火的“哇呀挖”，思考我软件开发的人生意义何在？

从爆火的“哇呀挖”，思考我软件开发的人生意义何在？

【 在什么样的花园里面，挖呀挖呀挖，种什么样的种子，开什么样的花，在小小的花园里面，挖呀挖呀挖，种小小的种子，开小小的花，在大大的花园里面，挖呀挖呀挖，种大大…
阅读更多...
看完这篇文章你就彻底懂啦｛保姆级讲解｝-----（LeetCode刷题202快乐数） 2023.5.9

看完这篇文章你就彻底懂啦｛保姆级讲解｝-----（LeetCode刷题202快乐数） 2023.5.9

目录 前言算法题（LeetCode刷题202快乐数）—（保姆级别讲解）分析题目算法思想（使用unordered_set实现哈希表） 结束语 前言 本文章一部分内容参考于《代码随想录》----如有侵权请联系作者删除即可&#xff0c…
阅读更多...
最新文章

Copyright @ 2022~2023