解决行迁移 案例

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;
表已删除。