Построение индекса в oracle

Пусть создан индекс по строковой колонке (не важно какой). Как известно, он будет представлять собой дерево, где каждый лист - подстрока. Каким образом устанавливается, какой длины подстрока в листе?

Ответ

Как известно, он будет представлять собой дерево, где каждый лист - подстрока.
Нет, это не так. В оф. документации говорится:
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row.
Каждая запись (leaf entry) в листке (leaf node) содержит пару key+value, соостоящая из полного значения индексированой колонки и адреса-указателя на соответствующую строку в таблице данных.
Например, построим два индекса с ключами одной и той же длины, один уникальный со случайными символами, и другой безсмысленный, с одним и тем же символом заполнителем.
create table tab1 as select dbms_random.string('u', 80) itemNo, rpad ('A', 80, 'A') itemDs from xmltable('1 to 100');
create unique index idx_tab1_uniq on tab1 (itemNo); create index idx_tab1_same on tab1 (itemDs);
exec dbms_stats.gather_table_stats(user, upper('tab1'));
select index_name, blevel, leaf_blocks, num_rows, clustering_factor from user_indexes where table_name=upper('tab1') order by 2,3;
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR ---------------- ---------- ----------- ---------- ----------------- IDX_TAB1_SAME 1 2 100 3 IDX_TAB1_UNIQ 1 2 100 63
Получили уровень ветвления 1 по 2 блока листьев .
Проведём анализ индексов:
analyze index idx_tab1_uniq validate structure offline; analyze index idx_tab1_same validate structure offline; -- выполнить после анализа каждого индекса select name, height, lf_blks, lf_rows, lf_rows_len, br_blks, br_rows, br_rows_len from index_stats t ;
NAME HEIGHT LF_BLKS LF_ROWS LF_ROWS_LEN ---------------- ---------- ---------- ---------- ----------- IDX_TAB1_UNIQ 2 2 100 9100 IDX_TAB1_SAME 2 2 100 9200
Оба индекса не намного отличаются по длине записей: lf_rows_len/lf_rows=avgLength 91 и 92 байта средняя длина записи.
Дамп блоков с листьями выглядит так (здесь только выдержка двух первых записей в первом листе каждого индекса):
Leaf block dump =============== header address 139975326998628=0x7f4e8ba4a064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 78 kdxcofbo 192=0xc0 kdxcofeo 1012=0x3f4 kdxcoavs 820 kdxlespl 0 kdxlende 0 kdxlenxt 50335373=0x3000e8d kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8032 row#0[7942] flag: -------, lock: 0, len=90 col 0; len 80; (80): 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 col 1; len 6; (6): 03 00 0e 7b 00 00 row#1[7852] flag: -------, lock: 0, len=90 col 0; len 80; (80): 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 col 1; len 6; (6): 03 00 0e 7b 00 01
Leaf block dump =============== header address 139975326998628=0x7f4e8ba4a064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 78 kdxcofbo 192=0xc0 kdxcofeo 1090=0x442 kdxcoavs 898 kdxlespl 0 kdxlende 0 kdxlenxt 50335365=0x3000e85 kdxleprv 0=0x0 kdxledsz 6 kdxlebksz 8032 row#0[7943] flag: -------, lock: 0, len=89, data:(6): 03 00 0e 7c 00 0a col 0; len 80; (80): 41 43 45 48 54 42 41 58 4f 52 45 5a 46 56 46 47 45 59 4a 51 53 49 4e 55 5a 56 54 4c 52 42 4e 42 42 48 54 4f 52 48 4a 52 4a 5a 55 43 42 44 43 51 49 43 43 48 47 42 56 41 58 58 54 4c 43 4e 59 50 59 59 4e 57 43 50 58 4f 59 44 53 55 4d 51 4b 48 row#1[7854] flag: -------, lock: 0, len=89, data:(6): 03 00 0e 7c 00 11 col 0; len 80; (80): 41 43 49 51 41 57 4b 4b 4d 4b 46 44 4d 57 59 54 54 42 43 56 49 4a 58 4f 44 4e 51 41 56 49 53 54 55 47 42 49 4c 51 48 4a 4f 45 52 42 58 53 47 41 4a 56 54 51 59 43 4f 44 50 59 51 56 45 43 44 47 51 48 4c 59 4e 46 44 48 47 55 58 47 42 45 50 4d