利用在线重定义降低表的高水位线

 

近日对某个线上库的表进行归档，原来有将近2千万条记录，归档后只剩不到400万，但是由于应用程序要对该表进行全表扫描，sql执行代价并没有降低。
决定使用在线重定义来降低其高水位线。
第一步：
建立中间表
-- Create table
create table justin_temp
(
  ID                    NUMBER(18) not null,
  SEND_TO               VARCHAR2(100),
  SUBJECT               VARCHAR2(200),
  CONTENT               VARCHAR2(4000),
  IS_SEND               NUMBER(1) default 0 not null,
  FAILED_AMOUNT         NUMBER(5) default 0 not null,
)

alter table justin_temp
  add constraint PK_JUSTIN_ID primary key (ID)
  using index;
第二步
执行在线重定义
SQL> begin
  2    dbms_redefinition.can_redef_table(uname => user,tname => 'justin');
  3  end;
  4  /
 
PL/SQL procedure successfully completed
 
SQL> begin
  2    dbms_redefinition.start_redef_table(uname => user,orig_table => 'justin',int_table => 'justin_TEMP');
  3  end;
  4  /
 
PL/SQL procedure successfully completed
 
SQL> begin
  2    dbms_redefinition.finish_redef_table(uname => user,orig_table => 'justin',int_table => 'justin_TEMP');
  3  end;
  4  /
 
PL/SQL procedure successfully completed

重定义之前表的统计信息

SQL> select num_rows,blocks from dba_tables where table_name='justin';
 
  NUM_ROWS     BLOCKS
---------- ----------
  3590554     570348

SQL> select header_file,header_block,bytes,blocks from dba_segments where segment_name='justin';
 
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS
----------- ------------ ---------- ----------
         15          107 4680843264     571392

重定义之后
SQL> select num_rows,blocks from dba_tables where table_name='justin';
 
  NUM_ROWS     BLOCKS
---------- ----------
   3590554     135412

SQL> select header_file,header_block,bytes,blocks from dba_segments where segment_name='justin';
 
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS
----------- ------------ ---------- ----------
         15       777323 1140850688     139264
由此大致可以估算出，此后每次全表扫描代价应该会是原来的1/4左右