Index Rebuild vs. Coalesce vs. Shrink Space

Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones) contains demo Differences between a Coalesce, Shrink Space and Rebuild

ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space, pct_used FROM index_stats;

SELECT n.name, s.value FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name = 'redo size';

ALTER INDEX bowie_stuff_i COALESCE;
-- or
ALTER INDEX bowie_stuff_i SHRINK SPACE COMPACT;
-- or
ALTER INDEX bowie_stuff_i SHRINK SPACE;
-- or
ALTER INDEX bowie_stuff_i REBUILD;
-- or
ALTER INDEX bowie_stuff_i REBUILD ONLINE NOLOGGING;

SELECT n.name, s.value FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic# AND n.name = 'redo size';

ANALYZE INDEX bowie_stuff_i VALIDATE STRUCTURE;

SELECT height, blocks, lf_blks, br_blks, del_lf_rows, btree_space, pct_used FROM index_stats;