ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Is the High Water Mark Reset When Moving or Import?

Is the High Water Mark Reset When Moving or Import?

原创 Linux操作系统 作者:spider0283 时间:2012-05-30 06:13:08 0 删除 编辑
[ID 220450.1]

PURPOSE
-------

This document explains if the High Water Mark is reset during the following 
procedures :

   - Moving a table to another tablespace
   - Export / Delete / Import
   - Export / Truncate / Import
 
SCOPE & APPLICATION
-------------------

For all DBAs who need to reclaim disk space.


*** *************************************************************
High Water Mark Before/After Moving a Table to Another Tablespace
*** *************************************************************

a. Create a table on tablespace USERS
   ----------------------------------
   SQL> create table t (a number, b number, c number, d number, e number, 
        f number) 
        tablespace USERS;

   Table created.

b. Load table with 200000 rows
   ---------------------------
   SQL> create or replace procedure populate (numrows in number) is
        fa number; fb number; fc number; fd number; fe number; ff number;
        begin
          dbms_random.Initialize(1234567);
          for i in 1..numrows loop
            fa:=mod(abs(dbms_random.random),10)+1991;            
            fb:=mod(abs(dbms_random.random),2);
            fc:=mod(abs(dbms_random.random),20);            
            fd:=mod(abs(dbms_random.random),30);
            fe:=mod(abs(dbms_random.random),40);
            ff:=mod(abs(dbms_random.random),10);
            insert into t values(fa,fb,fc,fd,fe,ff);
          if mod(i,100)=0 then commit; end if;
          end loop;
          dbms_random.Terminate;
        end;
        /

   Procedure created.
   SQL> set serveroutput on
   SQL> execute populate(200000);
   PL/SQL procedure successfully completed.

c. Delete rows
   -----------
   SQL> delete from t where a=1991 and b=0;
   9991 rows deleted.
   SQL> commit;
   Commit complete.
   ...
   SQL> delete from t where f=9; 
   13911 rows deleted.

d. Analyze the table to get the HWM value:
   --------------------------------------
   SQL> analyze table t compute statistics;
   Table analyzed.
 
   SQL> select num_rows, blocks, empty_blocks from dba_tables
        where table_name='T';

     NUM_ROWS     BLOCKS EMPTY_BLOCKS
   ---------- ---------- ------------
       126176       2654          550

e. Move the table to another tablespace TEST:
   -----------------------------------------
   SQL> alter table t move tablespace test;
   Table altered.

   SQL> select num_rows, blocks, empty_blocks 
        from dba_tables where table_name='T';

     NUM_ROWS     BLOCKS EMPTY_BLOCKS
   ---------- ---------- ------------

   The statistics are reset to NULL.

f. Reanalyze the table to get the new HWM value:
   --------------------------------------------    
   SQL> analyze table t compute statistics;
   Table analyzed.

   SQL> select num_rows, blocks, empty_blocks 
        from dba_tables where table_name='T';

     NUM_ROWS     BLOCKS EMPTY_BLOCKS
   ---------- ---------- ------------
       126176       1675          264


============>  YES, the HWM is reset.

To compare this method with other methods such as Export/Truncate/Import, the 
ALTER TABLE MOVE is more appropriate to optimize the reclaim of disk space, 
since you can specify new storage clause values for the new segment created :

   SQL> alter table t move tablespace TEST2 storage (initial 30k  next 50k);
   Table altered.

   SQL>  analyze table t compute statistics;
   Table analyzed.

   SQL>  select num_rows, blocks, empty_blocks from dba_tables 
         where table_name='T';

     NUM_ROWS     BLOCKS EMPTY_BLOCKS
   ---------- ---------- ------------
       126176       1675          274


The ALTER TABLE MOVE can even be a good method to optimize the HWM even if the
move occurs within the same tablespace


*** *************************************************************
High Water Mark After Exporting/Deleting/Importing a Table 
*** *************************************************************
Perform. the same steps a through d.

e. Export the table : 126176 rows exported
   ----------------

f. Delete all rows and reanalyze the table:
   ---------------------------------------
   SQL> select num_rows, blocks, empty_blocks from dba_tables
        where table_name='T';

     NUM_ROWS     BLOCKS EMPTY_BLOCKS
   ---------- ---------- ------------
       126176       2654          550

g. Import the table : 126176 rows imported
   ----------------

h. Reanalyze the table to table to get the new HWM value:
   -----------------------------------------------------  
   SQL> select num_rows, blocks, empty_blocks from dba_tables
        where table_name='T'; 

     NUM_ROWS     BLOCKS EMPTY_BLOCKS
   ---------- ---------- ------------
       126176       2654          550

============>  NO, the HWM is not reset.


*** *************************************************************
High Water Mark After Exporting/Truncating/Importing a Table 
*** *************************************************************
Perform. the same steps a through d.

e. Export the table : 126176 rows exported
   ----------------

f. Truncate the table keeping only the INITIAL extent:
   --------------------------------------------------
   SQL> truncate table t drop storage;
   Table truncated.

   SQL>  select num_rows, blocks, empty_blocks from dba_tables
         where table_name='T';

     NUM_ROWS     BLOCKS EMPTY_BLOCKS
   ---------- ---------- ------------
            0          0            4    
 
g. Import the table : 126176 rows imported
   ----------------

h. Reanalyze the table to table to get the new HWM value:
   -----------------------------------------------------  
   SQL> analyze table t compute statistics;
   Table analyzed.

   SQL> select num_rows, blocks, empty_blocks from dba_tables   
        where table_name='T';

     NUM_ROWS     BLOCKS EMPTY_BLOCKS
   ---------- ---------- ------------
       126176       1669          470

============>  YES, the HWM is reset.

You get the same behavior. with a TRUNCATE TABLE REUSE STORAGE, except that there
are more empty_blocks, unused (1535 empty blocks instead of 470 in our case).


RELATED DOCUMENTS
-----------------
Note:77635.1    How to Determine Real Space used by a Table (Below the High 
                  Water Mark)
Note:1019716.6  Script. to Report Table Fragmentation





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-731429/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    611147