重构表的方法

表重构的目的：降低HWM，减少逻辑读，减少表碎片，提高数据库性能

注意点：是否需要开启enable movement

                index rebuild online

                这个表涉及到的存储工程，存储过程被job调用，是否需要重新编译

               统计信息重新收集

              disable movement

             以上需要验证下

SQL> create table t as select * from dba_objects where 1=0;

Table created.

SQL> select extents,segment_name from dba_segments
  2  where segment_name='T' and wner='SYS';

   EXTENTS SEGMENT_NAME
---------- --------------------
         1 T

SQL> insert into t select * from dba_objects;

50934 rows created.

SQL>  select extents,segment_name from dba_segments
  2  where segment_name='T' and wner='SYS';

   EXTENTS SEGMENT_NAME
---------- --------------------
        21 T
SQL> delete from t;

50934 rows deleted.

SQL> select extents,segment_name from dba_segments
  2  where segment_name='T' and wner='SYS';

   EXTENTS SEGMENT_NAME
---------- --------------------
        21 T
SQL> alter table t move;

Table altered.

SQL> select extents,segment_name from dba_segments
  2  where segment_name='T' and wner='SYS';

   EXTENTS SEGMENT_NAME
---------- --------------------
         1 T
SQL> insert into t select * from dba_objects;

50934 rows created.

SQL> select bytes,extents,segment_name from dba_segments
  2  where segment_name='T' and wner='SYS';

     BYTES    EXTENTS SEGMENT_NAME
---------- ---------- --------------------
   6291456         21 T

SQL> set autotrace traceonly
SQL> select * from t;

50934 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 60070 |    10M|   157   (2)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    | 60070 |    10M|   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       4134  consistent gets
          0  physical reads
          0  redo size
    2638363  bytes sent via SQL*Net to client
      37837  bytes received via SQL*Net from client
       3397  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50934  rows processed

SQL> delete from t;

50934 rows deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 3335594643

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |   155   (0)| 00:00:02 |
|   1 |  DELETE            | T    |       |            |          |
|   2 |   TABLE ACCESS FULL| T    |     1 |   155   (0)| 00:00:02 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         94  recursive calls
      55388  db block gets
        824  consistent gets
          0  physical reads
   18867464  redo size
        832  bytes sent via SQL*Net to client
        706  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      50934  rows processed

SQL> select * from t;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   177 |   155   (0)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    |     1 |   177 |   155   (0)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        704  consistent gets
          0  physical reads
          0  redo size
       1139  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> alter table t move;

Table altered.

SQL> select * from t;                          

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   177 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |   177 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1139  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

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

转载于:http://blog.itpub.net/25602767/viewspace-691729/