首页 » Cloud, MySQL/TiDB/GoldenDB, ORACLE 9i-23ai, PostgreSQL/GaussDB » “alter table ” modify column in Oracle、MySQL、PostGreSQL(数据库比较系列十三)
“alter table ” modify column in Oracle、MySQL、PostGreSQL(数据库比较系列十三)
‘alter table’ DDL操作后期运维时比较常规的操作，但在oracle,MySQL，PostGreSQL中行为并不相同，Oracle还是三者中代价最低的，但是在Oracle DBA转向其它数据库运维时，以O的经验维护像MySQL、PostGreSQL时修改列的小动作可能会出现故障，比如空间耗尽、持续时间长、锁、执行计划变等现象。这篇分别测试一下三个数据库在ALTER TABLE modify column上的影响。
测试环境 ：oracle 19.3 VS postgresql 13.2 VS MySQL 8.0.20
ORACLE
drop table test_mod purge;
create table test_mod(id number(10),name varchar2(20));
insert into test_mod
select 1,'anbob.com' from dual connect by rownum<=10000;
create index idx_test_mod on test_mod(id);
SQL> @gts anbob test_mod
Gather Table Statistics for table anbob...
PL/SQL procedure successfully completed.
SQL>
select table_name,num_rows,blocks,LAST_ANALYZED,stale_stats from user_tab_statistics where table_name='TEST_MOD';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED STALE_S
------------------------------ ---------- ---------- ------------------- -------
TEST_MOD 10000 28 2022-11-24 11:01:09 NO
SQL> SQL> select index_name,num_rows,DISTINCT_KEYS,blevel,LAST_ANALYZED,stale_stats from user_ind_statistics where table_name='TEST_MOD';
INDEX_NAME NUM_ROWS DISTINCT_KEYS BLEVEL LAST_ANALYZED STA
------------------------------ ---------- ------------- ---------- ------------------- ---
IDX_TEST_MOD 10000 1 1 2022-11-24 11:01:09 NO
SQL> ALTER TABLE TEST_MOD MODIFY NAME VARCHAR2(30);
Table altered.
SQL> select index_name,num_rows,DISTINCT_KEYS,blevel,LAST_ANALYZED,stale_stats from user_ind_statistics where table_name='TEST_MOD';
INDEX_NAME NUM_ROWS DISTINCT_KEYS BLEVEL LAST_ANALYZED STA
------------------------------ ---------- ------------- ---------- ------------------- ---
IDX_TEST_MOD 10000 1 1 2022-11-24 11:01:09 NO
SQL>
SQL> select table_name,num_rows,blocks,LAST_ANALYZED,stale_stats from user_tab_statistics where table_name='TEST_MOD';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED STALE_S
------------------------------ ---------- ---------- ------------------- -------
TEST_MOD 10000 28 2022-11-24 11:01:09 NO
SQL> select object_name,statUS,LAST_DDL_TIME from user_objects where object_name in('TEST_MOD','IDX_TEST_MOD_ID');
OBJECT_NAME STATUS LAST_DDL_TIME
------------------------------ ------- -------------------
TEST_MOD VALID 2022-11-24 11:02:36
SQL> ALTER TABLE TEST_MOD MODIFY id number(20);
Table altered.
select table_name,num_rows,blocks,LAST_ANALYZED,stale_stats from user_tab_statistics where table_name='TEST_MOD';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED STALE_S
------------------------------ ---------- ---------- ------------------- -------
TEST_MOD 10000 28 2022-11-24 11:01:09 NO
select index_name,num_rows,DISTINCT_KEYS,blevel,LAST_ANALYZED,stale_stats from user_ind_statistics where table_name='TEST_MOD';
INDEX_NAME NUM_ROWS DISTINCT_KEYS BLEVEL LAST_ANALYZED STA
------------------------------ ---------- ------------- ---------- ------------------- ---
IDX_TEST_MOD 10000 1 1 2022-11-24 11:01:09 NO
SQL> select object_name,statUS,LAST_DDL_TIME from user_objects where object_name in('TEST_MOD','IDX_TEST_MOD_ID');
OBJECT_NAME STATUS LAST_DDL_TIME
------------------------------ ------- -------------------
TEST_MOD VALID 2022-11-24 11:03:09
SQL> select object_name,statUS,LAST_DDL_TIME from user_objects where object_name in('TEST_MOD','IDX_TEST_MOD');
OBJECT_NAME STATUS LAST_DDL_TIME
------------------------------ ------- -------------------
IDX_TEST_MOD VALID 2022-11-24 11:01:03
TEST_MOD VALID 2022-11-24 11:03:09
SQL> ALTER TABLE TEST_MOD MODIFY id number(10);
ALTER TABLE TEST_MOD MODIFY id number(10)
*
ERROR at line 1:
ORA-01440: column to be modified must be empty to decrease precision or scale
Note:
| 形为 | 统计信息 | 表重构 |
| 非索引列扩大长度 | 无影响 | 无影响 |
| 非索引列缩小长度 | 无影响 | 无影响 |
| 索引列扩大长度 | 无影响 | 无影响 |
| 索引列缩小长度 | 无影响 | 无影响 |
在Oracle中无论修改索引列还是非索引列的长度，扩大或缩小都不会影响表和索引的统计信息，也不会rebuild TABLE或索引，但是在缩小number类型列的精度时，必须要求表里数据为空，这类操作可以通过在线重定义或增减列完成，可能影响在增加列长度时不用像缩小列长度里对表中的数据检索。在Oracle可以做sql trace跟踪。
--缩小长度时
[oracle@oel7db1 ~]$ grep -i test /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_8502.trc
LOCK TABLE "ANBOB"."TEST_MOD" IN EXCLUSIVE MODE NOWAIT
alter table anbob.test_mod modify name varchar2(20)
select /*+ first_rows */ 1 from "ANBOB"."TEST_MOD" where LENGTHB("NAME") > 20
STAT #139703288612344 id=1 cnt=0 pid=0 pos=1 obj=80005 op='TABLE ACCESS FULL TEST_MOD (cr=30 pr=0 pw=0 str=1 time=1582 us cost=9 size=5000 card=500)'
value="TEST_MOD"
value="TEST_MOD"
--增加长度时
[oracle@oel7db1 ~]$ grep -i test /u01/app/oracle/diag/rdbms/anbob19c/anbob19c/trace/anbob19c_ora_8422.trc
LOCK TABLE "ANBOB"."TEST_MOD" IN EXCLUSIVE MODE NOWAIT
alter table anbob.test_mod modify name varchar2(50)
value="TEST_MOD"
value="TEST_MOD"
MySQL
MYSQL_root@localhost [anbob]> create table test_mod(id numeric(10),name varchar(20), CONSTRAINT idx_test_mod primary key (id)); Query OK, 0 rows affected (0.05 sec) MYSQL_root@localhost [anbob]> delimiter $$ create procedure add_data(in num int) begin declare i int default 1; while i<num do set i=i+1; insert into test_mod values (i,'anbob'); end while; end $$ delimiter ; MYSQL_root@localhost [anbob]> call add_data(10000); Query OK, 1 row affected (1 min 19.86 sec) MYSQL_root@localhost [(none)]> show status like '%rows_%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 9999 | | Innodb_rows_read | 0 | | Innodb_rows_updated | 0 | | Innodb_system_rows_deleted | 25 | | Innodb_system_rows_inserted | 27 | | Innodb_system_rows_read | 5763 | | Innodb_system_rows_updated | 325 | | Mysqlx_rows_sent | 0 | +-----------------------------+-------+ 9 rows in set (0.01 sec) MYSQL_root@localhost [anbob]> analyze table test_mod; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | anbob.test_mod | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.31 sec) MYSQL_root@localhost [anbob]> select TABLE_SCHEMA,table_name,column_name,CARDINALITY from information_schema.STATISTICS b where b.table_name='test_mod'; +--------------+------------+-------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CARDINALITY | +--------------+------------+-------------+-------------+ | anbob | test_mod | id | 10195 | +--------------+------------+-------------+-------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod'; +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1104 | anbob/test_mod | 33 | 5 | 47 | Dynamic | 0 | Single | 0 | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.01 sec) MYSQL_root@localhost [anbob]> select * from information_schema.INNODB_indexes where table_id=1104; +----------+---------+----------+------+----------+---------+-------+-----------------+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +----------+---------+----------+------+----------+---------+-------+-----------------+ | 193 | PRIMARY | 1104 | 3 | 4 | 4 | 47 | 50 | +----------+---------+----------+------+----------+---------+-------+-----------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> show index from test_mod; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | test_mod | 0 | PRIMARY | 1 | id | A | 10195 | NULL | NULL | | BTREE | | | YES | NULL | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select table_rows from information_schema.tables where table_name='test_mod'; +------------+ | TABLE_ROWS | +------------+ | 10195 | +------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select count(*) from test_mod; +----------+ | count(*) | +----------+ | 9999 | +----------+ 1 row in set (0.05 sec) MYSQL_root@localhost [anbob]> desc test_mod; +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | id | decimal(10,0) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) MYSQL_root@localhost [anbob]> alter table test_mod modify name varchar(30); Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0 MYSQL_root@localhost [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod'; +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1104 | anbob/test_mod | 33 | 5 | 47 | Dynamic | 0 | Single | 0 | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select table_rows from information_schema.tables where table_name='test_mod'; +------------+ | TABLE_ROWS | +------------+ | 10195 | +------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select TABLE_SCHEMA,table_name,column_name,CARDINALITY from information_schema.STATISTICS b where b.table_name='test_mod'; +--------------+------------+-------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CARDINALITY | +--------------+------------+-------------+-------------+ | anbob | test_mod | id | 10195 | +--------------+------------+-------------+-------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> alter table test_mod modify name varchar(20); Query OK, 9999 rows affected (0.67 sec) Records: 9999 Duplicates: 0 Warnings: 0 MYSQL_root@localhost [(none)]> show status like '%rows_%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 19998 | | Innodb_rows_read | 9999 | | Innodb_rows_updated | 0 | | Innodb_system_rows_deleted | 37 | | Innodb_system_rows_inserted | 41 | | Innodb_system_rows_read | 5843 | | Innodb_system_rows_updated | 353 | | Mysqlx_rows_sent | 0 | +-----------------------------+-------+ 9 rows in set (0.00 sec) MYSQL_root@localhost [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod'; +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1105 | anbob/test_mod | 33 | 5 | 48 | Dynamic | 0 | Single | 0 | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select * from information_schema.INNODB_indexes where table_id=1105; +----------+---------+----------+------+----------+---------+-------+-----------------+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +----------+---------+----------+------+----------+---------+-------+-----------------+ | 194 | PRIMARY | 1105 | 3 | 4 | 4 | 48 | 50 | +----------+---------+----------+------+----------+---------+-------+-----------------+ 1 row in set (0.01 sec) MYSQL_root@localhost [anbob]> select table_rows from information_schema.tables where table_name='test_mod'; +------------+ | TABLE_ROWS | +------------+ | 10195 | +------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select TABLE_SCHEMA,table_name,column_name,CARDINALITY from information_schema.STATISTICS b where b.table_name='test_mod'; +--------------+------------+-------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CARDINALITY | +--------------+------------+-------------+-------------+ | anbob | test_mod | id | 10195 | +--------------+------------+-------------+-------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> ALTER TABLE test_mod modify id numeric(20); Query OK, 9999 rows affected (0.65 sec) Records: 9999 Duplicates: 0 Warnings: 0 MYSQL_root@localhost [(none)]> show status like '%rows_%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 29997 | | Innodb_rows_read | 19998 | | Innodb_rows_updated | 0 | | Innodb_system_rows_deleted | 49 | | Innodb_system_rows_inserted | 55 | | Innodb_system_rows_read | 5925 | | Innodb_system_rows_updated | 381 | | Mysqlx_rows_sent | 0 | +-----------------------------+-------+ 9 rows in set (0.01 sec) MYSQL_root@localhost [anbob]> select TABLE_SCHEMA,table_name,column_name,CARDINALITY from information_schema.STATISTICS b where b.table_name='test_mod'; +--------------+------------+-------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CARDINALITY | +--------------+------------+-------------+-------------+ | anbob | test_mod | id | 10195 | +--------------+------------+-------------+-------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select table_rows from information_schema.tables where table_name='test_mod'; +------------+ | TABLE_ROWS | +------------+ | 10195 | +------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> delete from test_mod where id<=1000; Query OK, 999 rows affected (0.02 sec) MYSQL_root@localhost [anbob]> ALTER TABLE test_mod modify id numeric(20); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 MYSQL_root@localhost [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod'; +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1106 | anbob/test_mod | 33 | 5 | 49 | Dynamic | 0 | Single | 0 | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select * from information_schema.INNODB_indexes where table_id=1106; +----------+---------+----------+------+----------+---------+-------+-----------------+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD | +----------+---------+----------+------+----------+---------+-------+-----------------+ | 195 | PRIMARY | 1106 | 3 | 4 | 4 | 49 | 50 | +----------+---------+----------+------+----------+---------+-------+-----------------+ 1 row in set (0.01 sec) MYSQL_root@localhost [anbob]> ALTER TABLE test_mod modify id numeric(10); Query OK, 9000 rows affected (0.80 sec) Records: 9000 Duplicates: 0 Warnings: 0 MYSQL_root@localhost [(none)]> show status like '%rows_%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | Innodb_rows_deleted | 999 | | Innodb_rows_inserted | 38997 | | Innodb_rows_read | 29997 | | Innodb_rows_updated | 0 | | Innodb_system_rows_deleted | 72 | | Innodb_system_rows_inserted | 80 | | Innodb_system_rows_read | 6065 | | Innodb_system_rows_updated | 409 | | Mysqlx_rows_sent | 0 | +-----------------------------+-------+ 9 rows in set (0.00 sec) MYSQL_root@localhost [anbob]> select table_rows from information_schema.tables where table_name='test_mod'; +------------+ | TABLE_ROWS | +------------+ | 10195 | +------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select TABLE_SCHEMA,table_name,column_name,CARDINALITY from information_schema.STATISTICS b where b.table_name='test_mod'; +--------------+------------+-------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CARDINALITY | +--------------+------------+-------------+-------------+ | anbob | test_mod | id | 10195 | +--------------+------------+-------------+-------------+ 1 row in set (0.00 sec) MYSQL_root@localhost [anbob]> select * from information_schema.INNODB_TABLES where name='anbob/test_mod'; +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1107 | anbob/test_mod | 33 | 5 | 50 | Dynamic | 0 | Single | 0 | +----------+----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec)
Note:
| 形为 | 统计信息 | 表重构 |
| 非索引列扩大长度 | 无影响 | 无影响 |
| 非索引列缩小长度 | 无影响 | 重构 |
| 索引列扩大长度 | 无影响 | 重构 |
| 索引列缩小长度 | 无影响 | 重构 |
对于MYSQL缩小列长度和修改PK列都会导致表数据重组，如果是有大表小需要注意空间和时间，更多特性可以关注MYSQL Online DDL相关更新。如果alter 修改的列与原来相同并不会重构；并且即使重构后统计信息都不会影响；在mysql中information_schema.INNODB_TABLES的table_id, space可能简单认为是oracle的segment header判断是否重构，也可以从系统信息Innodb_rows_inserted 判断。
PostGreSQL
[local]:5432 postgres@postgres=# create table test_mod(id numeric(10),name varchar(20));
CREATE TABLE
insert into test_mod
select 1,'anbob.com'
from generate_series(1,10000);
create index idx_test_mod on test_mod(id);
analyze test_mod;
[local]:5432 postgres@postgres=# analyze test_mod;
ANALYZE
[local]:5432 postgres@postgres=# select pg_relation_filepath('test_mod');
pg_relation_filepath
----------------------
base/13580/26699
(1 row)
[local]:5432 postgres@postgres=# select pg_relation_filepath('idx_test_mod');
pg_relation_filepath
----------------------
base/13580/26702
(1 row)
[local]:5432 postgres@postgres=# select relname,relpages,reltuples from pg_class where relname in('test_mod','idx_test_mod');
relname | relpages | reltuples
--------------+----------+-----------
test_mod | 55 | 10000
idx_test_mod | 30 | 10000
(2 rows)
[local]:5432 postgres@postgres=# select * from pg_stat_user_tables where relname in('test_mod');
-[ RECORD 1 ]-------+------------------------------
relid | 26699
schemaname | public
relname | test_mod
seq_scan | 1
seq_tup_read | 10000
idx_scan | 0
idx_tup_fetch | 0
n_tup_ins | 10000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 10000
n_dead_tup | 0
n_mod_since_analyze | 0
n_ins_since_vacuum | 10000
last_vacuum |
last_autovacuum |
last_analyze | 2022-11-28 22:26:32.240225-05
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 1
autoanalyze_count | 0
[local]:5432 postgres@postgres=# select * from pg_stats where tablename='test_mod';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
| most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
+-------------------+------------------------+----------------------
public | test_mod | id | f | 0 | 5 | 1 | {1} | {1} | | 1
| | |
public | test_mod | name | f | 0 | 10 | 1 | {anbob.com} | {1} | | 1
| | |
(2 rows)
[local]:5432 postgres@postgres=# ALTER TABLE TEST_MOD alter column NAME TYPE varchar(30);
ALTER TABLE
[local]:5432 postgres@postgres=# select pg_relation_filepath('test_mod');
pg_relation_filepath
----------------------
base/13580/26699
(1 row)
[local]:5432 postgres@postgres=# select pg_relation_filepath('idx_test_mod');
pg_relation_filepath
----------------------
base/13580/26702
(1 row)
[local]:5432 postgres@postgres=# select relname,relpages,reltuples from pg_class where relname in('test_mod','idx_test_mod');
relname | relpages | reltuples
--------------+----------+-----------
test_mod | 55 | 10000
idx_test_mod | 30 | 10000
(2 rows)
[local]:5432 postgres@postgres=# select * from pg_stats where tablename='test_mod';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
| most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
+-------------------+------------------------+----------------------
public | test_mod | id | f | 0 | 5 | 1 | {1} | {1} | | 1
| | |
(1 row)
[local]:5432 postgres@postgres=# analyze test_mod;
ANALYZE
[local]:5432 postgres@postgres=# select relname,relpages,reltuples from pg_class where relname in('test_mod','idx_test_mod');
relname | relpages | reltuples
--------------+----------+-----------
test_mod | 55 | 10000
idx_test_mod | 30 | 10000
(2 rows)
[local]:5432 postgres@postgres=# select * from pg_stats where tablename='test_mod';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
| most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
+-------------------+------------------------+----------------------
public | test_mod | id | f | 0 | 5 | 1 | {1} | {1} | | 1
| | |
public | test_mod | name | f | 0 | 10 | 1 | {anbob.com} | {1} | | 1
| | |
(2 rows)
[local]:5432 postgres@postgres=# ALTER TABLE TEST_MOD alter column NAME TYPE VARCHAR(20);
ALTER TABLE
[local]:5432 postgres@postgres=# select pg_relation_filepath('test_mod');
pg_relation_filepath
----------------------
base/13580/26703
(1 row)
[local]:5432 postgres@postgres=# select pg_relation_filepath('idx_test_mod');
pg_relation_filepath
----------------------
base/13580/26706
(1 row)
[local]:5432 postgres@postgres=# select relname,relpages,reltuples from pg_class where relname in('test_mod','idx_test_mod');
relname | relpages | reltuples
--------------+----------+-----------
test_mod | 55 | 10000
idx_test_mod | 30 | 10000
(2 rows)
[local]:5432 postgres@postgres=# select * from pg_stats where tablename='test_mod';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
| most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
+-------------------+------------------------+----------------------
public | test_mod | id | f | 0 | 5 | 1 | {1} | {1} | | 1
| | |
(1 row)
[local]:5432 postgres@postgres=# analyze test_mod;
ANALYZE
[local]:5432 postgres@postgres=# ALTER TABLE TEST_MOD alter column id TYPE numeric(20);
ALTER TABLE
[local]:5432 postgres@postgres=# select pg_relation_filepath('test_mod');
pg_relation_filepath
----------------------
base/13580/26703
(1 row)
[local]:5432 postgres@postgres=# select pg_relation_filepath('idx_test_mod');
pg_relation_filepath
----------------------
base/13580/26706
(1 row)
[local]:5432 postgres@postgres=# select relname,relpages,reltuples from pg_class where relname in('test_mod','idx_test_mod');
relname | relpages | reltuples
--------------+----------+-----------
test_mod | 55 | 10000
idx_test_mod | 0 | 0
(2 rows)
[local]:5432 postgres@postgres=# select * from pg_stats where tablename='test_mod';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
| most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
+-------------------+------------------------+----------------------
public | test_mod | name | f | 0 | 10 | 1 | {anbob.com} | {1} | | 1
| | |
(1 row)
[local]:5432 postgres@postgres=# analyze test_mod;
ANALYZE
[local]:5432 postgres@postgres=# select relname,relpages,reltuples from pg_class where relname in('test_mod','idx_test_mod');
relname | relpages | reltuples
--------------+----------+-----------
test_mod | 55 | 10000
idx_test_mod | 30 | 10000
(2 rows)
[local]:5432 postgres@postgres=# select * from pg_stats where tablename='test_mod';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
| most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
+-------------------+------------------------+----------------------
public | test_mod | name | f | 0 | 10 | 1 | {anbob.com} | {1} | | 1
| | |
public | test_mod | id | f | 0 | 5 | 1 | {1} | {1} | | 1
| | |
(2 rows)
[local]:5432 postgres@postgres=# ALTER TABLE TEST_MOD alter column id TYPE numeric(10);
ALTER TABLE
[local]:5432 postgres@postgres=# select pg_relation_filepath('test_mod');
pg_relation_filepath
----------------------
base/13580/26709
(1 row)
[local]:5432 postgres@postgres=# select pg_relation_filepath('idx_test_mod');
pg_relation_filepath
----------------------
base/13580/26712
[local]:5432 postgres@postgres=# select relname,relpages,reltuples from pg_class where relname in('test_mod','idx_test_mod');
relname | relpages | reltuples
--------------+----------+-----------
test_mod | 55 | 10000
idx_test_mod | 30 | 10000
(2 rows)
[local]:5432 postgres@postgres=# select * from pg_stats where tablename='test_mod';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
| most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------
+-------------------+------------------------+----------------------
public | test_mod | name | f | 0 | 10 | 1 | {anbob.com} | {1} | | 1
| | |
(1 row)
NOTE:
| 形为 | 统计信息 | 表重构 |
| 非索引列扩大长度 | 列分布删除 | 无影响 |
| 非索引列缩小长度 | 列分布删除 | 重构 |
| 索引列扩大长度 | 列分布、索引统计信息删除 | 无影响 |
| 索引列缩小长度 | 列分布删除 | 重构 |
在PostgreSQL中修改列会导致列数据分布统计信息删除， 可能会导致执行计划出错，建议修改列后分析统计信息，同时缩小列长度会导致表和索引重构，注意空间与时间。索引列扩大长度索引的物理信息会自动删掉。
使用Systemtap跟踪Postgresql进程的操作
[root@oel7db1 ~]# stap pg_proc.stp >tt2.out
[local]:5432 postgres@postgres=# ALTER TABLE TEST_MOD alter column id TYPE numeric(30);
ALTER TABLE
[root@oel7db1 ~]# stap pg_proc.stp >tt3.out
[local]:5432 postgres@postgres=# ALTER TABLE TEST_MOD alter column NAME TYPE varchar(50);
ALTER TABLE
[root@oel7db1 ~]# grep -i statis tt2.out|sort|uniq -c
1 postgres: RemoveStatistics
[root@oel7db1 ~]# egrep -i 'tuples' tt2.out|sort|uniq -c
1 postgres: DeleteAttributeTuples
5 postgres: HeapTupleSatisfiesUpdate
523 postgres: HeapTupleSatisfiesVisibility
1 postgres: UtilityReturnsTuples
[root@oel7db1 ~]# egrep -i 'tuples' tt3.out|sort|uniq -c
2 postgres: HeapTupleSatisfiesUpdate
265 postgres: HeapTupleSatisfiesVisibility
1 postgres: UtilityReturnsTuples
代码
-- heap.c /* * RemoveStatistics --- remove entries in pg_statistic for a rel or column * * If attnum is zero, remove all entries for rel; else remove only the one(s) * for that column. */ void RemoveStatistics(Oid relid, AttrNumber attnum) /* * DeleteAttributeTuples * * Remove pg_attribute rows for the given relid. * * Note: this is shared by relation deletion and index deletion. It's * not intended for use anyplace else. */ void DeleteAttributeTuples(Oid relid)
Note:
修改列长度会触发RemoveStatistics函数，而清理列分布信息， 增加索引列长度函数增加DeleteAttributeTuples函数似乎是它清理了索引的物理统计信息。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。