ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 重构表的方法

重构表的方法

原创 Linux操作系统 作者:last_day_1983 时间:2011-04-05 20:20:12 0 删除 编辑

表重构的目的:降低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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: PL/SQL笔记
请登录后发表评论 登录
全部评论

注册时间:2011-04-05

  • 博文量
    4
  • 访问量
    19541