解决行迁移 案例

sql>select table_name,chain_cnt,stale_stats
from user_tab_statistics where table_name='ICME_SIMULATE_QA_STATUS';

table_name                 chain_cnt sta
-----------------------  -----------  -------
ICME_SIMULATE_QA_STATUS   501443      YES

--看出统计信息过旧

SQL> desc ICME_SIMULATE_QA_STATUS
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------------------
 STUDENT_STATUS_ID                         NOT NULL NUMBER(38)
 IC_CODE                                   NOT NULL VARCHAR2(9)
 QA_STATUS_ID                              NOT NULL NUMBER(38)
 CREATE_TIME                               NOT NULL DATE
 ADMIN_ID                                  NOT NULL NUMBER(38)
 ORG_ID                                    NOT NULL NUMBER(38)
 YEAR_ID                                            NUMBER(38)
 QA_STATUS_REMARK                                   VARCHAR2(500)

SQL> select max(length(QA_STATUS_REMARK)) from ICME_SIMULATE_QA_STATUS;

MAX(LENGTH(QA_STATUS_REMARK))
-----------------------------
                          101

sql> alter table ICME_SIMULATE_QA_STATUS modify  QA_STATUS_REMARK  varchar2(300);
减少不必要的长度

sql>analyze table ICME_SIMULATE_QA_STATUS compute statistics;
重新分析

SQL> select table_name,num_rows,avg_space,avg_row_len,chain_cnt,stale_stats from user_tab_statistics where table_name='ICME_SIMULATE_QA_STATUS';

TABLE_NAME                       NUM_ROWS  AVG_SPACE AVG_ROW_LEN  CHAIN_CNT  STALE_STATS
------------------------------ ---------- ---------- ----------- ----------  ---------
ICME_SIMULATE_QA_STATUS           4077696        854          68     874571   NO

坐整体行长度来看不是行链接，应该是行迁移

解决方法
SQL> @?/rdbms/admin/utlchain.sql
create table CHAINED_ROWS (
             *
第 1 行出现错误:
ORA-00955: name is already used by an existing object

SQL> select * from chained_rows;
未选定行

SQL> analyze table ICME_SIMULATE_QA_STATUS list chain rows;
analyze table ICME_SIMULATE_QA_STATUS list chain rows       
第 1 行出现错误:
ORA-00905: missing keyword


SQL> analyze table ICME_SIMULATE_QA_STATUS list chained rows;
表已分析。

SQL> select count(*) from chained_rows;
  COUNT(*)
----------
    874571
    
SQL> select table_name,pct_free from user_tables where table_name='ICME_SIMULATE_QA_STATUS';
TABLE_NAME                       PCT_FREE
------------------------------ ----------
ICME_SIMULATE_QA_STATUS                10

SQL> select count(*) from ICME_SIMULATE_QA_STATUS sta where exists (select null from chained_rows chrow where sta.rowid=chrow.head_rowid);

  COUNT(*)
----------
    874571

SQL> create table ICME_SIMULATE_QA_STATUS_tmp
  2  as
  3  select * from ICME_SIMULATE_QA_STATUS sta where exists (select null from chained_rows chrow where sta.rowid=chrow.head_rowid);

表已创建。

SQL> delete from ICME_SIMULATE_QA_STATUS sta where exists (select null from chained_rows chrow where sta.rowid=chrow.head_rowid);

已删除874571行。

SQL> select count(*) from  ICME_SIMULATE_QA_STATUS_tmp;
  COUNT(*)
----------
    874571
--再确认没问题再提交
SQL> commit;
提交完成。

SQL> alter table  ICME_SIMULATE_QA_STATUS pctfree 20;
表已更改。
--对于经常更新的表可以增加 pctfree 来预留大一点的空间

SQL> insert into  ICME_SIMULATE_QA_STATUS
  2  select * from  ICME_SIMULATE_QA_STATUS_tmp;

已创建874571行。

SQL> analyze table ICME_SIMULATE_QA_STATUS compute statistics;
表已分析。

SQL> select table_name,num_rows,avg_space,avg_row_len,chain_cnt,stale_stats from user_tab_statistics where table_name='ICME_SIMULATE_QA_STATUS';
TABLE_NAME                       NUM_ROWS  AVG_SPACE AVG_ROW_LEN  CHAIN_CNT STA
------------------------------ ---------- ---------- ----------- ---------- ---
ICME_SIMULATE_QA_STATUS           4077696       1157          66          1 NO

SQL> truncate table chained_rows;
表被截断。

SQL> drop table ICME_SIMULATE_QA_STATUS_tmp purge;
表已删除。