Question:
Let an index be created on a string column (it doesn't matter which one). As you know, it will be a tree, where each leaf is a substring.
How is it determined how long a substring is in a list?
Answer:
As you know, it will be a tree, where each leaf is a substring.
No, it's not. In office documentation says:
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row.
Each leaf entry in a leaf node contains a key+value pair consisting of the full value of the indexed column and a pointer address to the corresponding row in the data table.
For example, let's build two indexes with keys of the same length, one unique with random characters, and one meaningless with the same placeholder character.
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
We got branching level 1 for 2 blocks of leaves.
Let's analyze the indices:
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
Both indexes do not differ much in terms of record length:
lf_rows_len/lf_rows=avgLength 91 and 92 bytes average record length.
In the leaf block dump (here, only the excerpt of the first two records in the first leaf of each index), note the length of the records:
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