多动手多测试（二）关于降低水位索引失效的测试

前言

对表进行MOVE操作：ALTER TABLE TABLE_NAME MOVE;。若表上存在索引，则记得重建索引。

1.在TEST_TABLE上建立两个索引，一个在表所在表空间，一个在表外表空间（TEST_TABLE在表空间PT_DATE中）

2.查看并计算表占用空间大小

3.delete删除1400000条数后留下100000

4. 进行表分析后，重新计算占用空间

5.利用alter table move 回收表空间

6. 进行表分析后，重新计算占用空间

结论：alter table move 确实可以降低水位，回收表空间

7.再查看索引状态

结论：可以看到alter table move后表内表空间索引和表外表空间索引均失效

8.对两个索引进行重建后再查看索引状态

结论：重新变为可用状态

附录：sql语句

-- 创建表

CREATE TABLE TEST_TABLE
AS
   SELECT *
     FROM PT_IBPS_TRAN_MSG_HIST
    WHERE ROWNUM < 1500001;

-- 表计数

SELECT COUNT (1) FROM TEST_TABLE;

-- 查看水位

SELECT table_name,
       ROUND ( (blocks * 8) / 1024, 2) "高水位空间 m",
       ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2)
          "真实使用空间 m",
       ROUND ( (blocks * 10 / 100) * 8 / 1024, 2) "预留空间(pctfree) m",
       ROUND (
            (  blocks * 8
             - (num_rows * avg_row_len / 1024)
             - blocks * 8 * 10 / 100)
          / 1024,
          2)
          "浪费空间 m"
  FROM dba_tables
 WHERE temporary = 'N' AND table_name = 'TEST_TABLE' AND owner = 'IBPS_UAT';

-- 删除表数据

DELETE FROM TEST_TABLE
      WHERE ROWNUM < 1400001;

COMMIT;

-- 表分析

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS (OWNNAME            => 'IBPS_UAT',
                                  TABNAME            => 'TEST_TABLE',
                                  ESTIMATE_PERCENT   => '100',
                                  CASCADE            => TRUE,
                                  GRANULARITY        => 'ALL',
                                  DEGREE             => 4);
END;

-- 插入表

INSERT INTO TEST_TABLE
   SELECT *
     FROM PT_IBPS_TRAN_MSG_HIST
    WHERE ref_no NOT IN (SELECT ref_no FROM TEST_TABLE) AND ROWNUM < 1400001;

COMMIT;

-- 查看索引信息

SELECT index_name,
       index_type,
       tablespace_name,
       table_type,
       status
  FROM dba_indexes
 WHERE table_name = 'TEST_TABLE';

 -- 创建索引

CREATE INDEX TEST_TABLE_IDX1
   ON TEST_TABLE (REF_NO)
   LOGGING
   TABLESPACE PT_DATA
   PCTFREE 10
   INITRANS 2
   MAXTRANS 255
   STORAGE (INITIAL 64 K
            NEXT 1 M
            MAXSIZE UNLIMITED
            MINEXTENTS 1
            MAXEXTENTS UNLIMITED
            PCTINCREASE 0
            BUFFER_POOL DEFAULT
            FLASH_CACHE DEFAULT
            CELL_FLASH_CACHE DEFAULT)
   NOPARALLEL;

-- 降低表水位

ALTER TABLE TEST_TABLE MOVE;

-- 重建索引

ALTER INDEX TEST_TABLE_IDX1 REBUILD;

ALTER INDEX TEST_TABLE_IDX2 REBUILD;