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

#oracle #индексация #индексы

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

Ответы

Ответ 1

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


Нет, это не так. В оф. документации говорится:


  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