Всем привет, поднимаем тему после отпуска =)
Алгоритм переиспользования пустых блоков индексов довольно интересен, будет жалко, если обсуждение закончится ничем.
1.
2.
3.
4.
5.
6.
7.
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for HPUX: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Давайте начнем с едиственного кейса в теме от Elic.
Я, к сожалению, не понял, что он хотел показать =(
В кейсе всталяются 500к записей, потом удаляются, затем справа вставляется еще одна запись.
Далее показывается, что INDEX FULL SCAN (MIN/MAX) сканирует всю цепочку листовых блоков, что естественно, скан ищет первую неудаленную запись.
Нас же интересует переиспользование этих пустых блоков, хорошо покажет это вставка 250к блоков (половина объемы) справа, т.е. возрастающая последовательность.
Я повторил кейс Elic, добавив в конце свои statements (для удобства чтения я опустил результаты, оставив последние значащие).
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
SQL> create table qz_tab as select trunc(sysdate) + level/24/60/60 as dt from dual connect by level <= 500000;
SQL> create index qz_tab$i$dt on qz_tab(dt);
SQL> analyze index qz_tab$i$dt validate structure;
SQL> select lf_rows, lf_blks, br_rows, br_blks, del_lf_rows from index_stats;
SQL> delete qz_tab;
SQL> commit;
SQL> analyze index qz_tab$i$dt validate structure;
SQL> select lf_rows, lf_blks, br_rows, br_blks, del_lf_rows from index_stats;
SQL> insert into qz_tab values (trunc(sysdate) + 500001/24/60/60);
SQL> commit;
SQL> analyze index qz_tab$i$dt validate structure;
SQL> select lf_rows, lf_blks, br_rows, br_blks, del_lf_rows from index_stats;
SQL> set autot on
SQL> select min(dt) from qz_tab;
MIN(DT)
---------------
15-JUL-17
Execution Plan
----------------------------------------------------------
Plan hash value: 2270586670
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)|00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| QZ_TAB$I$DT | 1 | 9 | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
2819 consistent gets
0 physical reads
0 redo size
232 bytes sent via SQL*Net to client
250 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> insert into qz_tab select date '2018-01-01' + level/24/60/60 as dt from dual connect by level <= 250000;
SQL> commit;
SQL> select min(dt) from qz_tab;
MIN(DT)
---------------
15-JUL-17
Execution Plan
----------------------------------------------------------
Plan hash value: 2270586670
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)|00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| QZ_TAB$I$DT | 1 | 9 | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1509 consistent gets
0 physical reads
0 redo size
230 bytes sent via SQL*Net to client
250 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Как видим, количество чтений упало почти в 2 раза с 2819 до 1509, что как раз и показывает переиспользование пустых блоков.
Далее уже мой старый тест, там идет вставка и последующее удаление разных диапазонов записей.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
declare
procedure exec_dml( p_text in varchar2, p_sql in varchar2 ) is
begin
execute immediate p_sql;
dbms_output.put_line( p_text || ' : ' || p_sql || ';' );
end exec_dml;
procedure exec_sql( p_sql in varchar2 ) is
type t_tab is table of number;
v_tab t_tab;
v_sql_id varchar2(13);
v_child_number number;
v_gets number;
v_info varchar2(100);
begin
execute immediate p_sql bulk collect into v_tab;
select sql_id, child_number into v_sql_id, v_child_number
from ( select sql_id, child_number from v$sql where sql_text like p_sql || '%' order by last_active_time desc )
where rownum = 1;
select s.last_cr_buffer_gets + s.last_cu_buffer_gets, p.operation || ' ' || p.options into v_gets, v_info
from v$sql_plan p, v$sql_plan_statistics s
where p.sql_id = s.sql_id
and p.child_number = s.child_number
and p.address = s.address
and p.child_address = s.child_address
and p.id = s.operation_id
and p.sql_id = v_sql_id
and p.child_number = v_child_number
and p.object_name = 'IDX_TEST';
dbms_output.put_line( p_sql || ';' || ' rows = ' || to_char( v_tab.count ) || '; gets = ' || to_char( v_gets ) || ' [' || v_info || ']' );
exception
when others then
dbms_output.put_line( p_sql || '; ' || SQLERRM );
end exec_sql;
begin
execute immediate 'alter session set statistics_level=all';
execute immediate 'create table TAB_TEST( id number, text varchar2(20) )';
execute immediate 'create index IDX_TEST on TAB_TEST( ID )';
--===================================================================--
exec_dml( lpad( '=', 100 ,'=' ) || chr(10) || '==== Очищаем таблицу', 'truncate table TAB_TEST' );
exec_dml( '==== Вставляем 50000 записей', 'insert into TAB_TEST select rownum, ''test_'' || rownum from dual connect by rownum <= 50000' );
exec_dml( '==== Удаляем 30000 записей в середине', 'delete TAB_TEST where id between 10001 and 40000' );
commit;
dbms_output.put_line( chr(10) || '--Читаются пустые блоки' );
exec_sql( 'SELECT /* TEST 1.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 1.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 1.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 1.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 1.5 */ ID FROM TAB_TEST T WHERE ID < 40010' );
exec_dml( chr(10) || '==== Вставляем 20000 записей в конец', 'insert into TAB_TEST select rownum + 70000, ''test_'' || rownum from dual connect by rownum <= 20000' );
commit;
dbms_output.put_line( chr(10) || '--Часть пустых блоков переиспользованы повторно' );
exec_sql( 'SELECT /* TEST 2.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 2.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 2.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 2.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 2.5 */ ID FROM TAB_TEST T WHERE ID < 40010' );
exec_dml( chr(10) || '==== Вставляем 20000 записей в конец', 'insert into TAB_TEST select rownum + 90000, ''test_'' || rownum from dual connect by rownum <= 20000' );
commit;
dbms_output.put_line( chr(10) || '--Пустые блоки переиспользованы повторно' );
exec_sql( 'SELECT /* TEST 3.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 3.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 3.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 3.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 3.5 */ ID FROM TAB_TEST T WHERE ID < 40010' );
--===================================================================--
exec_dml( chr(10) || chr(10) || lpad( '=', 100 ,'=' ) || chr(10) || '==== Очищаем таблицу', 'truncate table TAB_TEST' );
exec_dml( '==== Вставляем 50000 записей', 'insert into TAB_TEST select rownum, ''test_'' || rownum from dual connect by rownum <= 50000' );
exec_dml( '==== Удаляем 30000 записей в начале', 'delete TAB_TEST where id between 1 and 30000' );
commit;
dbms_output.put_line( chr(10) || '--Читаются пустые блоки' );
exec_sql( 'SELECT /* TEST 4.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 4.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 4.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 4.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 4.5 */ ID FROM TAB_TEST T WHERE ID < 30010' );
exec_dml( chr(10) || '==== Вставляем 20000 записей в конец', 'insert into TAB_TEST select rownum + 70000, ''test_'' || rownum from dual connect by rownum <= 20000' );
commit;
dbms_output.put_line( chr(10) || '--Часть пустых блоков переиспользованы повторно' );
exec_sql( 'SELECT /* TEST 5.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 5.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 5.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 5.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 5.5 */ ID FROM TAB_TEST T WHERE ID < 30010' );
exec_dml( chr(10) || '==== Вставляем 20000 записей в конец', 'insert into TAB_TEST select rownum + 90000, ''test_'' || rownum from dual connect by rownum <= 20000' );
commit;
dbms_output.put_line( chr(10) || '--Пустые блоки переиспользованы повторно' );
exec_sql( 'SELECT /* TEST 6.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 6.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 6.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 6.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 6.5 */ ID FROM TAB_TEST T WHERE ID < 30010' );
--===================================================================--
exec_dml( chr(10) || chr(10) || lpad( '=', 100 ,'=' ) || chr(10) || '==== Очищаем таблицу', 'truncate table TAB_TEST' );
exec_dml( '==== Вставляем 50000 записей', 'insert into TAB_TEST select rownum, ''test_'' || rownum from dual connect by rownum <= 50000' );
exec_dml( '==== Удаляем все 50000 записей', 'delete TAB_TEST' );
commit;
dbms_output.put_line( chr(10) || '--Читаются пустые блоки' );
exec_sql( 'SELECT /* TEST 7.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 7.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 7.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 7.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 7.5 */ ID FROM TAB_TEST T WHERE ID < 30010' );
exec_dml( chr(10) || '==== Вставляем 20000 записей в конец', 'insert into TAB_TEST select rownum + 70000, ''test_'' || rownum from dual connect by rownum <= 20000' );
commit;
dbms_output.put_line( chr(10) || '--Часть пустых блоков переиспользованы повторно' );
exec_sql( 'SELECT /* TEST 8.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 8.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 8.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 8.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 8.5 */ ID FROM TAB_TEST T WHERE ID < 30010' );
exec_dml( chr(10) || '==== Вставляем 30000 записей в конец', 'insert into TAB_TEST select rownum + 90000, ''test_'' || rownum from dual connect by rownum <= 30000' );
commit;
dbms_output.put_line( chr(10) || '--Пустые блоки переиспользованы повторно' );
exec_sql( 'SELECT /* TEST 9.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001' );
exec_sql( 'SELECT /* TEST 9.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001' );
exec_sql( 'SELECT /* TEST 9.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001' );
exec_sql( 'SELECT /* TEST 9.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001' );
exec_sql( 'SELECT /*+ index(T IDX_TEST) TEST 9.5 */ ID FROM TAB_TEST T WHERE ID < 30010' );
--===================================================================--
execute immediate 'drop table TAB_TEST';
end;
/
Результаты:
====================================================================================================
==== Очищаем таблицу : truncate table TAB_TEST;
==== Вставляем 50000 записей : insert into TAB_TEST select rownum, 'test_' || rownum from dual connect by rownum <= 50000;
==== Удаляем 30000 записей в середине : delete TAB_TEST where id between 10001 and 40000;
--Читаются пустые блоки
SELECT /* TEST 1.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 2; gets = 126 [INDEX RANGE SCAN]
SELECT /* TEST 1.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 1; gets = 43 [INDEX RANGE SCAN]
SELECT /* TEST 1.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 1; gets = 85 [INDEX RANGE SCAN]
SELECT /* TEST 1.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 0; gets = 44 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 1.5 */ ID FROM TAB_TEST T WHERE ID < 40010; rows = 10009; gets = 164 [INDEX RANGE SCAN]
==== Вставляем 20000 записей в конец : insert into TAB_TEST select rownum + 70000, 'test_' || rownum from dual connect by rownum <= 20000;
--Часть пустых блоков переиспользованы повторно
SELECT /* TEST 2.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 2; gets = 46 [INDEX RANGE SCAN]
SELECT /* TEST 2.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 1; gets = 8 [INDEX RANGE SCAN]
SELECT /* TEST 2.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 1; gets = 45 [INDEX RANGE SCAN]
SELECT /* TEST 2.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 0; gets = 39 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 2.5 */ ID FROM TAB_TEST T WHERE ID < 40010; rows = 10009; gets = 84 [INDEX RANGE SCAN]
==== Вставляем 20000 записей в конец : insert into TAB_TEST select rownum + 90000, 'test_' || rownum from dual connect by rownum <= 20000;
--Пустые блоки переиспользованы повторно
SELECT /* TEST 3.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 2; gets = 3 [INDEX RANGE SCAN]
SELECT /* TEST 3.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 1; gets = 3 [INDEX RANGE SCAN]
SELECT /* TEST 3.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 1; gets = 3 [INDEX RANGE SCAN]
SELECT /* TEST 3.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 0; gets = 3 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 3.5 */ ID FROM TAB_TEST T WHERE ID < 40010; rows = 10009; gets = 41 [INDEX RANGE SCAN]
====================================================================================================
==== Очищаем таблицу : truncate table TAB_TEST;
==== Вставляем 50000 записей : insert into TAB_TEST select rownum, 'test_' || rownum from dual connect by rownum <= 50000;
==== Удаляем 30000 записей в начале : delete TAB_TEST where id between 1 and 30000;
--Читаются пустые блоки
SELECT /* TEST 4.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 10001; gets = 250 [INDEX FAST FULL SCAN]
SELECT /* TEST 4.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 0; gets = 43 [INDEX RANGE SCAN]
SELECT /* TEST 4.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 1; gets = 85 [INDEX RANGE SCAN]
SELECT /* TEST 4.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 1; gets = 44 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 4.5 */ ID FROM TAB_TEST T WHERE ID < 30010; rows = 9; gets = 123 [INDEX RANGE SCAN]
==== Вставляем 20000 записей в конец : insert into TAB_TEST select rownum + 70000, 'test_' || rownum from dual connect by rownum <= 20000;
--Часть пустых блоков переиспользованы повторно
SELECT /* TEST 5.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 10001; gets = 212 [INDEX FAST FULL SCAN]
SELECT /* TEST 5.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 0; gets = 35 [INDEX RANGE SCAN]
SELECT /* TEST 5.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 1; gets = 43 [INDEX RANGE SCAN]
SELECT /* TEST 5.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 1; gets = 10 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 5.5 */ ID FROM TAB_TEST T WHERE ID < 30010; rows = 9; gets = 43 [INDEX RANGE SCAN]
==== Вставляем 20000 записей в конец : insert into TAB_TEST select rownum + 90000, 'test_' || rownum from dual connect by rownum <= 20000;
--Пустые блоки переиспользованы повторно
SELECT /* TEST 6.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 10001; gets = 282 [INDEX FAST FULL SCAN]
SELECT /* TEST 6.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 0; gets = 2 [INDEX RANGE SCAN]
SELECT /* TEST 6.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 1; gets = 2 [INDEX RANGE SCAN]
SELECT /* TEST 6.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 1; gets = 2 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 6.5 */ ID FROM TAB_TEST T WHERE ID < 30010; rows = 9; gets = 2 [INDEX RANGE SCAN]
====================================================================================================
==== Очищаем таблицу : truncate table TAB_TEST;
==== Вставляем 50000 записей : insert into TAB_TEST select rownum, 'test_' || rownum from dual connect by rownum <= 50000;
==== Удаляем все 50000 записей : delete TAB_TEST;
--Читаются пустые блоки
SELECT /* TEST 7.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 0; gets = 126 [INDEX RANGE SCAN]
SELECT /* TEST 7.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 0; gets = 43 [INDEX RANGE SCAN]
SELECT /* TEST 7.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 0; gets = 85 [INDEX RANGE SCAN]
SELECT /* TEST 7.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 0; gets = 44 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 7.5 */ ID FROM TAB_TEST T WHERE ID < 30010; rows = 0; gets = 123 [INDEX RANGE SCAN]
==== Вставляем 20000 записей в конец : insert into TAB_TEST select rownum + 70000, 'test_' || rownum from dual connect by rownum <= 20000;
--Часть пустых блоков переиспользованы повторно
SELECT /* TEST 8.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 0; gets = 89 [INDEX RANGE SCAN]
SELECT /* TEST 8.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 0; gets = 33 [INDEX RANGE SCAN]
SELECT /* TEST 8.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 0; gets = 50 [INDEX RANGE SCAN]
SELECT /* TEST 8.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 0; gets = 21 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 8.5 */ ID FROM TAB_TEST T WHERE ID < 30010; rows = 0; gets = 71 [INDEX RANGE SCAN]
==== Вставляем 30000 записей в конец : insert into TAB_TEST select rownum + 90000, 'test_' || rownum from dual connect by rownum <= 30000;
--Пустые блоки переиспользованы повторно
SELECT /* TEST 9.1 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 40001; rows = 0; gets = 2 [INDEX RANGE SCAN]
SELECT /* TEST 9.2 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 20001; rows = 0; gets = 2 [INDEX RANGE SCAN]
SELECT /* TEST 9.3 */ ID FROM TAB_TEST WHERE ID BETWEEN 10000 AND 30001; rows = 0; gets = 2 [INDEX RANGE SCAN]
SELECT /* TEST 9.4 */ ID FROM TAB_TEST WHERE ID BETWEEN 20000 AND 30001; rows = 0; gets = 2 [INDEX RANGE SCAN]
SELECT /*+ index(T IDX_TEST) TEST 9.5 */ ID FROM TAB_TEST T WHERE ID < 30010; rows = 0; gets = 2 [INDEX RANGE SCAN]
И + еще накидал один дополнительно, тут вставляются в таблицу начальные 10000 значений, далее идут вставки в конец по 1000 и удаление сначала также по 1000 (500 итераций).
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
declare
v_ins_cnt number := 0;
v_del_cnt number := 0;
procedure put_index_stats( p_num in number, p_ins_cnt in number, p_del_cnt in number ) is
begin
execute immediate 'analyze index IDX_TEST validate structure';
for j in ( select height, blocks, lf_blks, lf_rows, br_blks, br_rows, del_lf_rows from index_stats ins where name = 'IDX_TEST' )
loop
dbms_output.put_line( lpad( p_num, 3, ' ' ) || '. blocks=' || to_char( j.blocks ) || ' lf_blks=' || to_char( j.lf_blks ) ||
' lf_rows=' || to_char( j.lf_rows ) || ' br_blks=' || to_char( j.br_blks ) || ' br_rows=' || to_char( j.br_rows ) ||
' del_lf_rows=' || to_char( j.del_lf_rows ) || ' [ins_cnt=' || to_char( p_ins_cnt ) || ' del_cnt=' || to_char( p_del_cnt ) || ']' );
end loop;
end put_index_stats;
begin
execute immediate 'create table TAB_TEST( id number, text varchar2(20) )';
execute immediate 'create index IDX_TEST on TAB_TEST( ID )';
execute immediate 'insert into TAB_TEST select rownum, ''test_'' || rownum from dual connect by rownum <= 10000';
for i in 1..500
loop
execute immediate 'insert into TAB_TEST select 10000 + rownum + ( ' || i || ' - 1 ) * 1000, ''test_'' || rownum from dual connect by rownum <= 1000';
v_ins_cnt := v_ins_cnt + sql%rowcount;
commit;
execute immediate 'delete TAB_TEST where ID < ' || i || ' * 1000';
v_del_cnt := v_del_cnt + sql%rowcount;
commit;
if mod( i, 10 ) = 0 then
put_index_stats( i, v_ins_cnt, v_del_cnt );
v_ins_cnt := 0;
v_del_cnt := 0;
end if;
end loop;
execute immediate 'drop table TAB_TEST';
end;
/
Результаты:
10. blocks=96 lf_blks=74 lf_rows=17123 br_blks=1 br_rows=73 del_lf_rows=7122 [ins_cnt=10000 del_cnt=9999]
20. blocks=96 lf_blks=87 lf_rows=19044 br_blks=1 br_rows=86 del_lf_rows=9043 [ins_cnt=10000 del_cnt=10000]
30. blocks=128 lf_blks=92 lf_rows=20526 br_blks=1 br_rows=91 del_lf_rows=10525 [ins_cnt=10000 del_cnt=10000]
40. blocks=128 lf_blks=92 lf_rows=20648 br_blks=1 br_rows=91 del_lf_rows=10647 [ins_cnt=10000 del_cnt=10000]
50. blocks=128 lf_blks=113 lf_rows=24436 br_blks=1 br_rows=112 del_lf_rows=14435 [ins_cnt=10000 del_cnt=10000]
60. blocks=128 lf_blks=114 lf_rows=24808 br_blks=1 br_rows=113 del_lf_rows=14807 [ins_cnt=10000 del_cnt=10000]
70. blocks=160 lf_blks=141 lf_rows=30302 br_blks=1 br_rows=140 del_lf_rows=20301 [ins_cnt=10000 del_cnt=10000]
80. blocks=160 lf_blks=147 lf_rows=30768 br_blks=1 br_rows=146 del_lf_rows=20767 [ins_cnt=10000 del_cnt=10000]
90. blocks=160 lf_blks=147 lf_rows=31610 br_blks=1 br_rows=146 del_lf_rows=21609 [ins_cnt=10000 del_cnt=10000]
100. blocks=192 lf_blks=148 lf_rows=32154 br_blks=1 br_rows=147 del_lf_rows=22153 [ins_cnt=10000 del_cnt=10000]
110. blocks=192 lf_blks=159 lf_rows=34021 br_blks=1 br_rows=158 del_lf_rows=24020 [ins_cnt=10000 del_cnt=10000]
120. blocks=192 lf_blks=177 lf_rows=37257 br_blks=1 br_rows=176 del_lf_rows=27256 [ins_cnt=10000 del_cnt=10000]
130. blocks=192 lf_blks=177 lf_rows=37554 br_blks=1 br_rows=176 del_lf_rows=27553 [ins_cnt=10000 del_cnt=10000]
140. blocks=192 lf_blks=177 lf_rows=37954 br_blks=1 br_rows=176 del_lf_rows=27953 [ins_cnt=10000 del_cnt=10000]
150. blocks=224 lf_blks=191 lf_rows=40871 br_blks=1 br_rows=190 del_lf_rows=30870 [ins_cnt=10000 del_cnt=10000]
160. blocks=224 lf_blks=205 lf_rows=43230 br_blks=1 br_rows=204 del_lf_rows=33229 [ins_cnt=10000 del_cnt=10000]
170. blocks=224 lf_blks=207 lf_rows=42956 br_blks=1 br_rows=206 del_lf_rows=32955 [ins_cnt=10000 del_cnt=10000]
180. blocks=224 lf_blks=207 lf_rows=42345 br_blks=1 br_rows=206 del_lf_rows=32344 [ins_cnt=10000 del_cnt=10000]
190. blocks=224 lf_blks=207 lf_rows=42732 br_blks=1 br_rows=206 del_lf_rows=32731 [ins_cnt=10000 del_cnt=10000]
200. blocks=256 lf_blks=208 lf_rows=43525 br_blks=1 br_rows=207 del_lf_rows=33524 [ins_cnt=10000 del_cnt=10000]
210. blocks=256 lf_blks=208 lf_rows=43168 br_blks=1 br_rows=207 del_lf_rows=33167 [ins_cnt=10000 del_cnt=10000]
220. blocks=256 lf_blks=208 lf_rows=43211 br_blks=1 br_rows=207 del_lf_rows=33210 [ins_cnt=10000 del_cnt=10000]
230. blocks=256 lf_blks=208 lf_rows=42597 br_blks=1 br_rows=207 del_lf_rows=32596 [ins_cnt=10000 del_cnt=10000]
240. blocks=256 lf_blks=208 lf_rows=42143 br_blks=1 br_rows=207 del_lf_rows=32142 [ins_cnt=10000 del_cnt=10000]
250. blocks=256 lf_blks=208 lf_rows=42607 br_blks=1 br_rows=207 del_lf_rows=32606 [ins_cnt=10000 del_cnt=10000]
260. blocks=256 lf_blks=208 lf_rows=43126 br_blks=1 br_rows=207 del_lf_rows=33125 [ins_cnt=10000 del_cnt=10000]
270. blocks=256 lf_blks=229 lf_rows=47697 br_blks=1 br_rows=228 del_lf_rows=37696 [ins_cnt=10000 del_cnt=10000]
280. blocks=256 lf_blks=238 lf_rows=49974 br_blks=1 br_rows=237 del_lf_rows=39973 [ins_cnt=10000 del_cnt=10000]
290. blocks=288 lf_blks=238 lf_rows=49170 br_blks=1 br_rows=237 del_lf_rows=39169 [ins_cnt=10000 del_cnt=10000]
300. blocks=288 lf_blks=238 lf_rows=48596 br_blks=1 br_rows=237 del_lf_rows=38595 [ins_cnt=10000 del_cnt=10000]
310. blocks=288 lf_blks=238 lf_rows=48443 br_blks=1 br_rows=237 del_lf_rows=38442 [ins_cnt=10000 del_cnt=10000]
320. blocks=288 lf_blks=242 lf_rows=48379 br_blks=1 br_rows=241 del_lf_rows=38378 [ins_cnt=10000 del_cnt=10000]
330. blocks=288 lf_blks=251 lf_rows=48937 br_blks=1 br_rows=250 del_lf_rows=38936 [ins_cnt=10000 del_cnt=10000]
340. blocks=288 lf_blks=252 lf_rows=49602 br_blks=1 br_rows=251 del_lf_rows=39601 [ins_cnt=10000 del_cnt=10000]
350. blocks=288 lf_blks=254 lf_rows=49987 br_blks=1 br_rows=253 del_lf_rows=39986 [ins_cnt=10000 del_cnt=10000]
360. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
370. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
380. blocks=288 lf_blks=262 lf_rows=52799 br_blks=1 br_rows=261 del_lf_rows=42798 [ins_cnt=10000 del_cnt=10000]
390. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
400. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
410. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
420. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
430. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
440. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
450. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
460. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
470. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
480. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
490. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
500. blocks=288 lf_blks=262 lf_rows=52557 br_blks=1 br_rows=261 del_lf_rows=42556 [ins_cnt=10000 del_cnt=10000]
Тут видно, что хотя размер и растет постоянно, но алгоритм переиспользования пустых блоков работает.
Для сравнения, размер индекса без удаления записей (т.е. имуляция того, что пустые блоки остаются навсегда) - 2105 lf_blks.
