Oracle high water level

Oracle High Water (HWM: High Water Mark) 2017-04-14 02:49:28

SELECT a.owner

A.index_name

A.index_type

A.partitioned

A.status

B.status p_status

B.composite

FROM dba_indexes a

LEFT JOIN

Dba_ind_partitions b

ON a.owner = b.index_owner AND a.index_name = b.index_name

WHERE a.owner ='& owner' AND a.table_name ='& table_name'

Direct rebuild online nologging parallel for general indexes

For partitioned indexes, you must rebuild each partition separately

For combined partitioned indexes, you must rebuild each subpartition separately.

Move reduces HWM by moving data, so more disk space is needed. Shrink produces more undo and redo through delete and insert.

Shrink space shrinks to the minimum value of the data store, alter table move (without parameters) shrinks to the value specified by initial, or you can specify the size of the contraction with alter table test move storage (initial 500k), which achieves the shrink space effect.

In short, using Move is more efficient, but it can lead to index invalidation. Shrink produces undo and redo, which are relatively slow.