Эта статья посвящена обсуждению структуры записей в индексах (помните, я собирался часть выпусков посвящать индексам ;). По мотивам ответа Тома Кайта на вопрос, заданный 14 июня 2003 года.
Системная информация в индексах
Том,
После анализа индекса (analyze ... validate) я поделил LF_ROWS_LEN на LF_ROWS и обнаружил значение на 12 байтов больше, чем длина ключа. Я ожидал увеличения только на 6 байтов - размер rowid, а откуда взялись другие 6 байтов? Может, я неправильно считаю?
SQL> analyze index IDX_TBLACCOUNT validate structure; Index analyzed SQL> select * from index_stats; HEIGHT 3 BLOCKS 18056 NAME IDX_TBLACCOUNT PARTITION_NAME LF_ROWS 5796880 LF_BLKS 16868 LF_ROWS_LEN 75359440 LF_BLK_LEN 7996 BR_ROWS 16867 BR_BLKS 57 BR_ROWS_LEN 237396 BR_BLK_LEN 8028 DEL_LF_ROWS 7655 DEL_LF_ROWS_LEN 99515 DISTINCT_KEYS 20 MOST_REPEATED_KEY 4228703 BTREE_SPACE 135334124 USED_SPACE 75596836 PCT_USED 56 ROWS_PER_KEY 289844 BLKS_GETS_PER_ACCESS 144925.5 PRE_ROWS 0 PRE_ROWS_LEN 0
LF_ROWS_LEN/LF_ROWS = 75359440/5796880 = 13
Длина столбца - 1 байт (varchar2(1)).
SQL> select * from user_ind_columns where index_name = 'IDX_TBLACCOUNT'; INDEX_NAME IDX_TBLACCOUNT TABLE_NAME TBLACCOUNT COLUMN_NAME AC_STATUS COLUMN_POSITION 1 COLUMN_LENGTH 1 DESCEND ASC SQL> select * from user_tab_columns where table_name='TBLACCOUNT' and column_name='AC_STATUS'; TABLE_NAME TBLACCOUNT COLUMN_NAME AC_STATUS DATA_TYPE VARCHAR2 DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH 1 DATA_PRECISION DATA_SCALE NULLABLE Y COLUMN_ID 67 DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT 17 LOW_VALUE 30 HIGH_VALUE 74 DENSITY 0.05882352 NUM_NULLS 0 NUM_BUCKETS 1 LAST_ANALYZED 6/14/2003 SAMPLE_SIZE 235526 CHARACTER_SET_NAME CHAR_CS CHAR_COL_DECL_LENGTH 1 GLOBAL_STATS NO USER_STATS NO AVG_COL_LEN 1
Анализирую другой индекс, по той же таблице, и снова получаю 12 дополнительных байтов:
SQL> analyze index idx_tblaccount_stssch validate structure; Index analyzed SQL> select * from index_stats; HEIGHT 3 BLOCKS 18845 NAME IDX_TBLACCOUNT_STSSCH PARTITION_NAME LF_ROWS 5794493 LF_BLKS 17096 LF_ROWS_LEN 121620457 LF_BLK_LEN 7996 BR_ROWS 17095 BR_BLKS 53 BR_ROWS_LEN 390029 BR_BLK_LEN 8028 DEL_LF_ROWS 5268 DEL_LF_ROWS_LEN 110621 DISTINCT_KEYS 22291 MOST_REPEATED_KEY 553444 BTREE_SPACE 137125100 USED_SPACE 122010486 PCT_USED 89 ROWS_PER_KEY 259.94764703 BLKS_GETS_PER_ACCESS 133.473823516217 PRE_ROWS 0 PRE_ROWS_LEN 0
LF_ROWS_LEN/LF_ROWS = 121620457/5794493 = 20.99 (у нас 12880 строк со значениями NULL в первом столбце)
Получаем снова DATE (7 байтов) + разделитель? - прокомментируй, пожалуйста, (1 байт) + varchar2(1) (1 байт) = 9 байтов.
12 байтов использовано системой для своих целей.
SQL> select * from user_tab_columns where table_name='TBLACCOUNT' and column_name='AC_SCHEDULETIME'; TABLE_NAME TBLACCOUNT COLUMN_NAME AC_SCHEDULETIME DATA_TYPE DATE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH 7 DATA_PRECISION DATA_SCALE NULLABLE Y COLUMN_ID 72 DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT 4941 LOW_VALUE 78640714110101 HIGH_VALUE C7C70C1F0E0201 DENSITY 0.00020238 NUM_NULLS 12880 NUM_BUCKETS 1 LAST_ANALYZED 6/14/2003 SAMPLE_SIZE 235526 CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS NO USER_STATS NO AVG_COL_LEN 7
Ответ Тома Кайта
Системой используется 4/5 байтов.
Столбец типа varchar2 имеет начальный байт длины, так что varchar2(1) занимает, минимум, 2 байта. Надо также учесть индикатор null-значения. Значение rowid тоже хранится как "строка" (с начальными байтами длины).
Итак, для ключа типа varchar2(1) имеем:
1 байт "длины", 1 байт "данных", 1 байт "длины rowid", 6 байтов данных rowid = 9 байтов.
При тестировании я получаю 12 байтов на строку в версии 9203 для ОС RedHat Linux на платформе Intel. Это очень легко определить:
ops$tkyte@ORA920> create table t ( x varchar2(20) ); Table created. ops$tkyte@ORA920> create index t_idx on t(x); Index created. ops$tkyte@ORA920> begin 2 for i in 1 .. 20 3 loop 4 execute immediate 'truncate table t'; 5 6 insert into t 7 select rpad(chr(rownum),i,chr(rownum)) 8 from all_objects 9 where rownum <= 255; 10 11 execute immediate 'analyze index t_idx validate structure'; 12 for x in ( select lf_rows_len, lf_rows, lf_rows_len/lf_rows bytes_per_entry 13 from index_stats ) 14 loop 15 dbms_output.put_line 16 ( 'Width = ' // i // ' lf_rows_len = ' // x.lf_rows_len // 17 ' bytes/entry = ' // x.bytes_per_entry ); 18 end loop; 19 end loop; 20 end; 21 / Width = 1 lf_rows_len = 3315 bytes/entry = 13 Width = 2 lf_rows_len = 3570 bytes/entry = 14 Width = 3 lf_rows_len = 3825 bytes/entry = 15 Width = 4 lf_rows_len = 4080 bytes/entry = 16 Width = 5 lf_rows_len = 4335 bytes/entry = 17 Width = 6 lf_rows_len = 4590 bytes/entry = 18 Width = 7 lf_rows_len = 4845 bytes/entry = 19 Width = 8 lf_rows_len = 5100 bytes/entry = 20 Width = 9 lf_rows_len = 5355 bytes/entry = 21 Width = 10 lf_rows_len = 5610 bytes/entry = 22 Width = 11 lf_rows_len = 5865 bytes/entry = 23 Width = 12 lf_rows_len = 6120 bytes/entry = 24 Width = 13 lf_rows_len = 6375 bytes/entry = 25 Width = 14 lf_rows_len = 6630 bytes/entry = 26 Width = 15 lf_rows_len = 6885 bytes/entry = 27 Width = 16 lf_rows_len = 7140 bytes/entry = 28 Width = 17 lf_rows_len = 7395 bytes/entry = 29 Width = 18 lf_rows_len = 7650 bytes/entry = 30 Width = 19 lf_rows_len = 7905 bytes/entry = 31 Width = 20 lf_rows_len = 8160 bytes/entry = 32 PL/SQL procedure successfully completed.
Итак, при длине 20 мы имеем:
длина 1+
данные 20+
длина 1+
данные 6
--
32-28 = 4
В каждой строке - на 4 байта "больше", а если добавить еще один большой столбец:
ops$tkyte@ORA920> drop table t; Table dropped. ops$tkyte@ORA920> create table t ( x varchar2(20), y char(100) default 'x' ); Table created. ops$tkyte@ORA920> create index t_idx on t(x,y); Index created. ops$tkyte@ORA920> begin 2 for i in 1 .. 20 3 loop 4 execute immediate 'truncate table t'; 5 6 insert into t (x) 7 select rpad(chr(rownum),i,chr(rownum)) 8 from all_objects 9 where rownum <= 255; 10 11 execute immediate 'analyze index t_idx validate structure'; 12 for x in ( select lf_rows_len, lf_rows, lf_rows_len/lf_rows bytes_per_entry 13 from index_stats ) 14 loop 15 dbms_output.put_line 16 ( 'Width = ' // i // ' lf_rows_len = ' // x.lf_rows_len // 17 ' bytes/entry = ' // x.bytes_per_entry ); 18 end loop; 19 end loop; 20 end; 21 / Width = 1 lf_rows_len = 29070 bytes/entry = 114 Width = 2 lf_rows_len = 29325 bytes/entry = 115 Width = 3 lf_rows_len = 29580 bytes/entry = 116 Width = 4 lf_rows_len = 29835 bytes/entry = 117 Width = 5 lf_rows_len = 30090 bytes/entry = 118 Width = 6 lf_rows_len = 30345 bytes/entry = 119 Width = 7 lf_rows_len = 30600 bytes/entry = 120 Width = 8 lf_rows_len = 30855 bytes/entry = 121 Width = 9 lf_rows_len = 31110 bytes/entry = 122 Width = 10 lf_rows_len = 31365 bytes/entry = 123 Width = 11 lf_rows_len = 31620 bytes/entry = 124 Width = 12 lf_rows_len = 31875 bytes/entry = 125 Width = 13 lf_rows_len = 32130 bytes/entry = 126 Width = 14 lf_rows_len = 32385 bytes/entry = 127 Width = 15 lf_rows_len = 32640 bytes/entry = 128 Width = 16 lf_rows_len = 32895 bytes/entry = 129 Width = 17 lf_rows_len = 33150 bytes/entry = 130 Width = 18 lf_rows_len = 33405 bytes/entry = 131 Width = 19 lf_rows_len = 33660 bytes/entry = 132 Width = 20 lf_rows_len = 33915 bytes/entry = 133 PL/SQL procedure successfully completed.
Можно ожидать:
длина 1
данные 20
длина 1 (да, CHAR - всего лишь VARCHAR2, дополненный пробелами - длина тоже хранится)
данные 100
длина 1
данные 6
---
133 - 129 = 4 дополнительных байта
Комментарий читателя от 15 июня 2003 года
Интересно, зачем для значения rowid длина, - что, идентификаторы строк могут быть переменной длины? И откуда берутся эти 4 байта? Зачем они нужны?
Ответ Тома Кайта
Они просто "есть" (то есть, нужны)
Комментарий читателя от 18 июня 2003 года
Вот дамп блока:
row#0[536] flag: -----, lock: 0 col 0; len 20; (20): 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 01 col 1; len 6; (6): 02 40 03 8d 00 00 row#1[566] flag: -----, lock: 0 col 0; len 20; (20): 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 02 col 1; len 6; (6): 02 40 03 8d 00 01 row#2[596] flag: -----, lock: 0 col 0; len 20; (20): 03 03 03 03 03 03 03 03 03 03 03 03 03 03 03 03 03 03 03 03 col 1; len 6; (6): 02 40 03 8d 00 02 row#3[626] flag: -----, lock: 0 col 0; len 20; (20): 04 04 04 04 04 04 04 04 04 04 04 04 04 04 04 04 04 04 04 04 col 1; len 6; (6): 02 40 03 8d 00 03
Судя по нему, каждая строка занимает 30 байтов: строки начинаются со смещений 536, 566, 596, 626... Не мог бы ты объяснить, откуда взялись 2 байта в твоих результатах?
Ответ Тома Кайта
Я не занимаюсь интерпретацией результатов дампов. Вы изучаете "отчет" о блоке, а не сам блок. См. поля flags, row#, и т.д. Считайте, что это "данные, которые необходимы серверу для управления данными пользователя"
Все просто - сколько надо, столько система и использует.