ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 降低HWM的方法之move

降低HWM的方法之move

原创 Linux操作系统 作者:Power08 时间:2013-09-14 20:48:59 0 删除 编辑
1.move表重建索引
    alter table move
    alter index rebuild online tablespace  
2.SQL> create table test as select * from dba_objects;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.

SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables  WHERE table_name='TEST';

TABLE_NAME   NUM _ROWS     BLOCKS EMPTY_BLOCKS
------------------- ----------                  ----------       ------------
TEST                  17345                       230           25 
SQL> set autotrace traceonly

SQL> select count(*) from test;

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

SQL> delete test where rownum < 10000;

9999 rows deleted.

SQL> commit;

Commit complete.
SQL> analyze table test compute statistics;
    
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables  WHERE table_name='TEST';

TABLE_NAME  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- -------------------------------
TEST   7346        230           25

SQL> select count(*) from test;


Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    64   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |  7346 |    64   (0)| 00:00:01 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        234  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
从上面可以看出总的行数减少了很多 ,但所使用的块没有减少,全表扫描块总数也没减少。

move表:alter table test move 或 alter table test move tablespace
SQL> alter table test move;
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM user_tables  WHERE table_name='TEST';
TABLE_NAME      NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- -----------
TEST           7346        102            1

SQL> select count(*) from test;

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |  7346 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        106  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
可以看出move表后,表所使用的块及全表扫描块都减少了

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

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

注册时间:2013-07-01

  • 博文量
    17
  • 访问量
    44496