В данной публикации рассматривается отличие, якобы эквивалентных,
COALESCE и
Согласно документации ,
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
----------
56
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
----------
56
----- 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
----------
56
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
Комментариев нет:
Отправить комментарий