首页 » MySQL, ORACLE, ORACLE [C]系列, PostgreSQL » “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函数似乎是它清理了索引的物理统计信息。

打赏

对不起,这篇文章暂时关闭评论。