oracle表维护操作move update index与move online的一点差异

在巡检时发现部分表的分区的索引比表大了几千倍，虽然频繁发生UPDATE,DELETE会导致索引比表大，但大这么多的很少见到，经过分析发现是这些表之前维护时做过MOVE操作，使用了update index选项。12c开始支持move online操作，move online操作在完成时会重建索引，不会产生move完成后表变小了，但索引仍然很大的问题，下面记录一下重现该过程并对比move online操作。

move update index

SQL> create table dbmt.t1(id number,c varchar2(1024));


Table created.

SQL> insert into dbmt.t1 select rownum,rpad('a',1024,'b') from dual connect by rownum<100000;

99999 rows created.

SQL>  create index dbmt.idx_t1 on dbmt.t1(c);


Index created.


SQL> @seg dbmt.t1

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ---------- ---------- ----------
       120 DBMT                 T1                                                            TABLE                USERS                                                              7680        878     570147

SQL> @seg dbmt.idx_t1

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ---------- ---------- ----------
       128 DBMT                 IDX_T1                                                        INDEX                USERS                                                              8192        878     570163

SQL> delete dbmt.t1;

99999 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table DBMT.t1 move update indexes;


Table altered.


SQL> @seg dbmt.t1

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ---------- ---------- ----------
         0 DBMT                 T1                                                            TABLE                USERS                                                                 8        878     574919

SQL> @seg dbmt.idx_t1

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ---------- ---------- ----------
       128 DBMT                 IDX_T1                                                        INDEX                USERS                                                              8192        878     570163
复制

move online

SQL> drop table dbmt.t1;

Table dropped.

SQL> create table dbmt.t1(id number,c varchar2(1024));

Table created.

SQL>  insert into dbmt.t1 select rownum,rpad('a',1024,'b') from dual connect by rownum<100000;

99999 rows created.

SQL>  create index dbmt.idx_t1 on dbmt.t1(c);

Index created.

SQL>  @seg dbmt.t1

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ---------- ---------- ----------
       120 DBMT                 T1                                                            TABLE                USERS                                                              7680        878     570147

SQL>  @seg dbmt.idx_t1

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ---------- ---------- ----------
       128 DBMT                 IDX_T1                                                        INDEX                USERS                                                              8192        878     574927

SQL> delete dbmt.t1;
commit;
99999 rows deleted.

SQL> 

Commit complete.

SQL> alter table DBMT.t1 move online;

Table altered.

SQL> @seg DBMT.t1

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ---------- ---------- ----------
         0 DBMT                 T1                                                            TABLE                USERS                                                                 8        878     574955

SQL> @seg DBMT.idx_t1

    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                                              BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------------------------------------ ---------- ---------- ----------
         0 DBMT                 IDX_T1                                                        INDEX                USERS                                                                 8        878     574947

SQL> @o dbmt.idx_t1

owner                     object_name                    object_type        status           OID      D_OID CREATED           LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- -----------------
DBMT                      IDX_T1                         INDEX              VALID        7134518    7134525 20210924 10:19:29 20210924 10:20:05
复制