降低HWM的方法之move

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29014732/viewspace-772720/