数据库优化 ---表移动

问题：一个表只有127行，查询时花费了一千七百多个IO。

过程:
sql>  analyze table ZY_SALERESOURCE compute statistics;

SQL> select  TABLE_NAME,NUM_ROWS,BLOCKS, CHAIN_CNT,EMPTY_BLOCKS, LAST_ANALYZED from user_tables where TABLE_NAME='ZY_SALERESOURCE'; 

TABLE_NAME                       NUM_ROWS     BLOCKS  CHAIN_CNT EMPTY_BLOCKS
------------------------------ ---------- ---------- ---------- ------------
LAST_ANAL
---------
ZY_SALERESOURCE                       127       7804          0          132
16-MAY-11

发现：BLOCKS： 7804



解决：
SQL> alter table ZY_SALERESOURCE  MOVE;
SQL> ALTER TABLE ZY_SALERESOURCE  ENABLE ROW MOVEMENT;                                                                             
Table altered.

SQL> ALTER TABLE ZY_SALERESOURCE  SHRINK SPACE;                                                                                    
Table altered.
SQL> analyze table ZY_SALERESOURCE compute statistics;
SQL> SELECT BLOCKS,EXTENTS FROM USER_SEGMENTS  where segment_name='ZY_SALERESOURCE';                                                

    BLOCKS    EXTENTS
---------- ----------
        16          2
SQL>SELECT BLOCKS FROM USER_TABLES  where TABLE_name='ZY_SALERESOURCE';                                                            

    BLOCKS
----------
        13



记住： 做完表移动后，一定要重新做表的分析

---------------------------------------------------------------------------
1. user_segments 的blocks表示：该段目前分配了多少块。段是一个区一个区分配的，每次空间不够了，就分配一个区。尽管你只差一个数据块，oracle也要分一个区给你。

2. 表的移动和伸缩的区别：
   (1) 移动：alter table t move;
            alter index t_index rebuid online;
       使用场合：
          i.表做update之后发生行迁移时，可以使用
          ii. 表需要移动到其他的表空间
           alter table aa move tablespace user;
   (2)伸缩：
       alter table t enable row movement;
       alter table t shrink space;
      使用场合：做delete操作后,与碎片整理相似
      因为：delete高水位不变

   (3)例子：

做move操作:

SQL> select BLOCKS,NUM_ROWS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name ='ZY_BUYRESOURCE';      

    BLOCKS   NUM_ROWS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
      4906      13610           86       7063          0         349

SQL> ALTER TABLE ZY_BUYRESOURCE MOVE;                                                                                               

Table altered.

SQL> analyze table ZY_buyRESOURCE compute statistics;                                                                               
analyze table ZY_buyRESOURCE compute statistics
*
ERROR at line 1:
ORA-01502: index 'STEEL.PK_ZY_BUYRESOURCE' or partition of such index is in
unusable state


SQL> select BLOCKS,NUM_ROWS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name ='ZY_BUYRESOURCE';      

    BLOCKS   NUM_ROWS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
      4906      13610           86       7063          0         349

SQL> ALTER INDEX STEEL.PK_ZY_BUYRESOURCE REBUILD ONLINE;                                                                            

Index altered.

SQL> analyze table ZY_buyRESOURCE compute statistics;                                                                               

Table analyzed.

SQL> select BLOCKS,NUM_ROWS, EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tables where table_name ='ZY_BUYRESOURCE';      

    BLOCKS   NUM_ROWS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
       688      13610           80        903          0         349
 SQL> SELECT BLOCKS,EXTENTS FROM USER_SEGMENTS  where segment_name='ZY_BUYRESOURCE';                                                 

    BLOCKS    EXTENTS
---------- ----------
       768         21
做了move后，没有释放表空间

-------------------------------------------
做伸缩操作：
SQL> SELECT BLOCKS FROM USER_TABLES  where TABLE_name='ZY_BUYRESOURCE';                                                             

    BLOCKS
----------
       688

SQL> SELECT BLOCKS,EXTENTS FROM USER_SEGMENTS  where segment_name='ZY_BUYRESOURCE';                                                 

    BLOCKS    EXTENTS
---------- ----------
       768         21

SQL> ALTER TABLE ZY_BUYRESOURCE ENABLE ROW MOVEMENT;                                                                                

Table altered.

SQL> ALTER TABLE ZY_BUYRESOURCE SHRINK SPACE;                                                                                       

Table altered.

SQL> SELECT BLOCKS,EXTENTS FROM USER_SEGMENTS  where segment_name='ZY_BUYRESOURCE';                                                 

    BLOCKS    EXTENTS
---------- ----------
       688         21

SQL> SELECT BLOCKS FROM USER_TABLES  where TABLE_name='ZY_BUYRESOURCE';                                                             

    BLOCKS
----------
       688

SQL> analyze table ZY_buyRESOURCE compute statistics;                                                                               

Table analyzed.

SQL> SELECT BLOCKS,EXTENTS FROM USER_SEGMENTS  where segment_name='ZY_BUYRESOURCE';                                                 

    BLOCKS    EXTENTS
---------- ----------
       688         21

SQL> SELECT BLOCKS FROM USER_TABLES  where TABLE_name='ZY_BUYRESOURCE';                                                             

    BLOCKS
----------
       688

发现：释放了表空间。