About Oracle: Coalesce vs Shrink

В данной публикации рассматривается отличие, якобы эквивалентных,
COALESCE и SHRINK SPACE COMPACT

Согласно документации ,

команда coalesce имеет следующие отличия от rebuild

Rebuild Index	Coalesce Index
Quickly moves index to another tablespace	Cannot move index to another tablespace
Higher costs: requires more disk space	Lower costs: does not require more disk space
Creates new tree, shrinks height if applicable	Coalesces leaf blocks within same branch of tree
Enables you to quickly change storage and tablespace parameters without having to drop the original index.	Quickly frees up index leaf blocks for use.

Так же, документация

говорит, что

Specifying ALTER INDEX ... SHRINK SPACE COMPACT is equivalent to specifying ALTER INDEX ... COALESCE.

Проведем небольшой тест

SQL> create table d(x varchar2(512 byte), y number) tablespace users;

Table created.

SQL> create index i_d on d(x) tablespace users;

Index created.

SQL> begin

for i in 1..450 loop

insert into d select lpad('*',512,'*'), i from dual;

commit;

end loop;

end;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'D', cascade=>true);

PL/SQL procedure successfully completed.

SQL> select blocks from dba_segments where segment_name='I_D';

BLOCKS

----------

SQL> select a.pct_free, a.blevel, a.leaf_blocks from dba_indexes a where index_name='I_D';

PCT_FREE BLEVEL LEAF_BLOCKS

---------- ---------- -----------

10 2 42

Распределение данных по листьям выглядит следующим образом

----- begin tree dump

branch: 0x100290c 16787724 (0: nrow: 4, level: 2)

branch: 0x100292d 16787757 (-1: nrow: 6, level: 1)

leaf: 0x1002910 16787728 (-1: nrow: 11 rrow: 11)

leaf: 0x100290f 16787727 (0: nrow: 11 rrow: 11)

leaf: 0x100291c 16787740 (1: nrow: 11 rrow: 11)

leaf: 0x100291f 16787743 (2: nrow: 6 rrow: 6)

leaf: 0x100291e 16787742 (3: nrow: 7 rrow: 7)

leaf: 0x100291d 16787741 (4: nrow: 6 rrow: 6)

branch: 0x100292e 16787758 (0: nrow: 11, level: 1)

leaf: 0x100290e 16787726 (-1: nrow: 8 rrow: 8)

leaf: 0x100290d 16787725 (0: nrow: 11 rrow: 11)

leaf: 0x100292a 16787754 (1: nrow: 7 rrow: 7)

leaf: 0x100292b 16787755 (2: nrow: 11 rrow: 11)

leaf: 0x100292c 16787756 (3: nrow: 15 rrow: 15)

leaf: 0x1002930 16787760 (4: nrow: 5 rrow: 5)

leaf: 0x100292f 16787759 (5: nrow: 5 rrow: 5)

leaf: 0x1002920 16787744 (6: nrow: 15 rrow: 15)

leaf: 0x1002919 16787737 (7: nrow: 15 rrow: 15)

leaf: 0x100291a 16787738 (8: nrow: 15 rrow: 15)

leaf: 0x100291b 16787739 (9: nrow: 10 rrow: 10)

branch: 0x100293a 16787770 (1: nrow: 10, level: 1)

leaf: 0x100293f 16787775 (-1: nrow: 11 rrow: 11)

leaf: 0x1002940 16787776 (0: nrow: 15 rrow: 15)

leaf: 0x1002948 16787784 (1: nrow: 9 rrow: 9)

leaf: 0x1002939 16787769 (2: nrow: 11 rrow: 11)

leaf: 0x100293b 16787771 (3: nrow: 11 rrow: 11)

leaf: 0x1002947 16787783 (4: nrow: 8 rrow: 8)

leaf: 0x100293e 16787774 (5: nrow: 5 rrow: 5)

leaf: 0x100293c 16787772 (6: nrow: 15 rrow: 15)

leaf: 0x100293d 16787773 (7: nrow: 11 rrow: 11)

leaf: 0x1002954 16787796 (8: nrow: 8 rrow: 8)

branch: 0x1002957 16787799 (2: nrow: 15, level: 1)

leaf: 0x1002955 16787797 (-1: nrow: 11 rrow: 11)

leaf: 0x1002956 16787798 (0: nrow: 15 rrow: 15)

leaf: 0x1002946 16787782 (1: nrow: 9 rrow: 9)

leaf: 0x1002942 16787778 (2: nrow: 15 rrow: 15)

leaf: 0x1002943 16787779 (3: nrow: 15 rrow: 15)

leaf: 0x1002944 16787780 (4: nrow: 15 rrow: 15)

leaf: 0x1002945 16787781 (5: nrow: 11 rrow: 11)

leaf: 0x1002952 16787794 (6: nrow: 11 rrow: 11)

leaf: 0x1002953 16787795 (7: nrow: 10 rrow: 10)

leaf: 0x1002963 16787811 (8: nrow: 9 rrow: 9)

leaf: 0x1002967 16787815 (9: nrow: 11 rrow: 11)

leaf: 0x1002968 16787816 (10: nrow: 11 rrow: 11)

leaf: 0x1002962 16787810 (11: nrow: 12 rrow: 12)

leaf: 0x1002958 16787800 (12: nrow: 15 rrow: 15)

leaf: 0x1002951 16787793 (13: nrow: 7 rrow: 7)

Удалим 400 строк из таблицы.

SQL> delete from d where y<=400;

400 row deleted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'D', cascade=>true);

PL/SQL procedure successfully completed.

SQL> select a.pct_free, a.blevel, a.leaf_blocks from dba_indexes a where index_name='I_D';

PCT_FREE BLEVEL LEAF_BLOCKS

---------- ---------- -----------

10 2 6

SQL> select blocks from dba_segments where segment_name='I_D';

BLOCKS

----------

----- begin tree dump

branch: 0x100290c 16787724 (0: nrow: 4, level: 2)

branch: 0x100292d 16787757 (-1: nrow: 6, level: 1)

...
...

branch: 0x100292e 16787758 (0: nrow: 11, level: 1)

...
...

branch: 0x100293a 16787770 (1: nrow: 10, level: 1)

...
...

branch: 0x1002957 16787799 (2: nrow: 15, level: 1)

leaf: 0x1002955 16787797 (-1: nrow: 11 rrow: 0)

leaf: 0x1002956 16787798 (0: nrow: 15 rrow: 0)

leaf: 0x1002946 16787782 (1: nrow: 9 rrow: 0)

leaf: 0x1002942 16787778 (2: nrow: 15 rrow: 0)

leaf: 0x1002943 16787779 (3: nrow: 15 rrow: 0)

leaf: 0x1002944 16787780 (4: nrow: 15 rrow: 0)

leaf: 0x1002945 16787781 (5: nrow: 11 rrow: 0)

leaf: 0x1002952 16787794 (6: nrow: 11 rrow: 1)

leaf: 0x1002953 16787795 (7: nrow: 10 rrow: 10)

leaf: 0x1002963 16787811 (8: nrow: 9 rrow: 9)

leaf: 0x1002967 16787815 (9: nrow: 11 rrow: 11)

leaf: 0x1002968 16787816 (10: nrow: 11 rrow: 11)

leaf: 0x1002962 16787810 (11: nrow: 12 rrow: 8)

leaf: 0x1002958 16787800 (12: nrow: 15 rrow: 0)

leaf: 0x1002951 16787793 (13: nrow: 7 rrow: 0)

----- end tree dump

Делаем coalesce

SQL> alter index i_d coalesce;

Index altered.

SQL> exec dbms_stats.gather_table_stats(user,'D', cascade=>true);

PL/SQL procedure successfully completed.

SQL>select a.pct_free, a.blevel, a.leaf_blocks from dba_indexes a where index_name='I_D';

PCT_FREE BLEVEL LEAF_BLOCKS

---------- ---------- -----------

10 2 4

SQL> select blocks from dba_segments where segment_name='I_D';

BLOCKS

----------

SQL>ALTER SESSION SET EVENTS 'immediate trace name treedump level 63123';

Session altered.

----- begin tree dump

branch: 0x100290c 16787724 (0: nrow: 1, level: 2)

branch: 0x1002957 16787799 (-1: nrow: 4, level: 1)

leaf: 0x1002952 16787794 (-1: nrow: 13 rrow: 13)

leaf: 0x1002963 16787811 (0: nrow: 13 rrow: 13)

leaf: 0x1002967 16787815 (1: nrow: 13 rrow: 13)

leaf: 0x1002962 16787810 (2: nrow: 11 rrow: 11)

----- end tree dump

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM index_stats;

HEIGHT BLOCKS LF_BLKS BR_BLKS BTREE_SPACE PCT_USED

---------- ---------- ---------- ---------- ----------- ----------

3 56 4 2 48040 58

Теперь все в норме.

Алгоритм coalesce следующий:

начинаем с крайне левого(первый выделенный блок индексу) листка, проверяя, если в листике актуальные строки(rrow:!=0). Если строк нет, то блок «выбрасываем»
когда встречаем заполненный блок, смотрим, чтобы свободного пространства в нем было 50% +pctfree. Если блок подходит, то начинаем инсертить в него строки из следующего блока до момента, пока в текущем блоке не достигнем pctfree.
если следующий блок в пункте 2 был опустошен полностью, то «выбрасываем» его.
переходим к следующему блоку учитывая условия из п.1 и п.2 и опять по новой.

Не смотря на то, что tree-dump показывает 4 листовых блока, dba_segments содержит 56 блоков, так как coalesce не понижает HWM сегмента индекса.

Повторим тот же эксперимент, но вместо coalesce используем SHRINK SPACE COMPACT

----- begin tree dump

branch: 0x10029d4 16787924 (0: nrow: 4, level: 2)

   branch: 0x10029ed 16787949 (-1: nrow: 11, level: 1)

      leaf: 0x10029d8 16787928 (-1: nrow: 11 rrow: 0)

      leaf: 0x10029d7 16787927 (0: nrow: 11 rrow: 0)

      leaf: 0x10029dc 16787932 (1: nrow: 11 rrow: 0)

      leaf: 0x10029df 16787935 (2: nrow: 6 rrow: 0)

      leaf: 0x10029de 16787934 (3: nrow: 7 rrow: 0)

      leaf: 0x10029dd 16787933 (4: nrow: 6 rrow: 0)

      leaf: 0x10029d6 16787926 (5: nrow: 8 rrow: 0)

      leaf: 0x10029d5 16787925 (6: nrow: 11 rrow: 0)

      leaf: 0x10029ea 16787946 (7: nrow: 7 rrow: 0)

      leaf: 0x10029eb 16787947 (8: nrow: 11 rrow: 0)

      leaf: 0x10029ec 16787948 (9: nrow: 15 rrow: 0)

   branch: 0x10029ee 16787950 (0: nrow: 8, level: 1)

...

...

   branch: 0x1002a14 16787988 (1: nrow: 12, level: 1)

...

...

   branch: 0x1002a03 16787971 (2: nrow: 11, level: 1)

      leaf: 0x1002a02 16787970 (-1: nrow: 15 rrow: 0)

      leaf: 0x1002a04 16787972 (0: nrow: 15 rrow: 0)

      leaf: 0x1002a05 16787973 (1: nrow: 11 rrow: 0)

      leaf: 0x1002a12 16787986 (2: nrow: 11 rrow: 1)

      leaf: 0x1002a13 16787987 (3: nrow: 10 rrow: 10)

      leaf: 0x1002a23 16788003 (4: nrow: 9 rrow: 9)

      leaf: 0x1002a27 16788007 (5: nrow: 11 rrow: 11)

      leaf: 0x1002a28 16788008 (6: nrow: 11 rrow: 11)

      leaf: 0x1002a22 16788002 (7: nrow: 12 rrow: 8)

      leaf: 0x1002a18 16787992 (8: nrow: 15 rrow: 0)

      leaf: 0x1002a11 16787985 (9: nrow: 7 rrow: 0)

----- end tree dump

--после SHRINK SPACE COMPACT

SQL>SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM index_stats;

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------

         3         56         42          5      375972         69

----- begin tree dump

branch: 0x10029d4 16787924 (0: nrow: 1, level: 2)

   branch: 0x10029d9 16787929 (-1: nrow: 4, level: 1)

      leaf: 0x10029d8 16787928 (-1: nrow: 13 rrow: 13)

      leaf: 0x10029d6 16787926 (0: nrow: 13 rrow: 13)

      leaf: 0x10029d5 16787925 (1: nrow: 13 rrow: 13)

      leaf: 0x10029d7 16787927 (2: nrow: 11 rrow: 11)

----- end tree dump

Из чего можно сделать вывод, что так же как и coalesce,  SHRINK SPACE COMPACT не урезает фактический размер индекса, то есть не обновляет карту сегмента, но в отличии от coalesce «готовит почву» для последующего SHRINK , перенося строки в крайне левые блоки индекса, который уменьшит физический размер индекса просто сбросив HWM сегмента до нужного значения

SQL> alter index i_d shrink space;

Index altered.

SQL>ANALYZE INDEX i_d VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT height, blocks, lf_blks, br_blks, btree_space, pct_used FROM index_stats;

    HEIGHT     BLOCKS    LF_BLKS    BR_BLKS BTREE_SPACE   PCT_USED

---------- ---------- ---------- ---------- ----------- ----------

         3         16          4          2       48040         58

----- begin tree dump

branch: 0x10029d4 16787924 (0: nrow: 1, level: 2)

   branch: 0x10029d9 16787929 (-1: nrow: 4, level: 1)

      leaf: 0x10029d8 16787928 (-1: nrow: 13 rrow: 13)

      leaf: 0x10029d6 16787926 (0: nrow: 13 rrow: 13)

      leaf: 0x10029d5 16787925 (1: nrow: 13 rrow: 13)

      leaf: 0x10029d7 16787927 (2: nrow: 11 rrow: 11)

----- end tree dump

Расположение строк в листьях не изменилось, но количество блоков, выделенных сегменту, уменьшилось.  Если же сделать сначала coalesce, а потом SHRINK SPACE, то получим что при SHRINK SPACE помимо сброса HWM придется перенести «ужатые строки после coalesce» в физическое начало индекса.

То есть, если нам нужно просто дефрагментировать индекс, то используем coalesce. Если нужно дефрагментировать и в будущем предполагаем, что будем усекать, то сначала  SHRINK SPACE COMPACT а потом SHRINK SPACE.

PS: а дока, говоря об эквивалентности, как обычно, не договаривает J

About Oracle

суббота, 20 февраля 2010 г.

Coalesce vs Shrink

Комментариев нет:

Отправить комментарий

суббота, 20 февраля 2010 г.

Coalesce vs Shrink

Комментариев нет:

Отправить комментарий

суббота, 20 февраля 2010 г.