Binary Search Tree 22 Binary Tree Binary Search

Binary Search Tree (2/2) 인덱스 개념과 종류 Binary Tree와 Binary Search Tree 예제 Binary Trees Binary Search Trees 28 12 10 15 32 14 11 30 5 30 42 70 37 Page 6 44 Database by Yang-Sae Moon

AVL 트리 (3/3) 인덱스 개념과 종류 6 12 4 10 3 4 8 8 8 10 6 12 4 16 3 10 6 11 15 17 5 9 16 15 8 9 3 6 7 2 7 4 10 4 2 6 5 6 9 16 14 18 12 Page 11 Database by Yang-Sae Moon

m-way Search Tree (2/3) 인덱스 개념과 종류 예제 (3 -way search tree) 100 140 Internal Nodes 30 ^ 10 ^ 25 20 ^ 60 40 ^ 55 110 120 50 70 90 170 150 160 180 200 220 Leaf Nodes 키 값 Ki는 실제로 (Ki, Ai)를 의미하며, 여기서 Ai는 데이터 레코드의 주소를 의미함 Page 14 Database by Yang-Sae Moon

B-트리 (1/2) 인덱스 개념과 종류 Bayer & Mc. Creight가 제안 R. Bayer and C. Mc. Creight, “Organization and Maintenance of Large Ordered Indexes, ” Acta Informatica 1, pp. 173 -189, 1972. B-tree? : balanced m-way search tree • m-way search tree의 균형을 유지하기 위하여 효율적인 검색/삽입/삭제 알고리즘을 제공 • B+-tree와 함께 가장 많이 사용되는 인덱스 방법 Page 16 Database by Yang-Sae Moon

B-트리의 노드 구조 (2/2) 인덱스 개념과 종류 B-트리의 예 (3 -way) a 69 root node ^ b 19 d 26 ^ j k 15 43 e 16 7 c 18 l 20 128 f 40 60 m 30 36 g n 42 o 50 58 h 100 ^ p 62 65 r 110 120 130 Page 19 internal nodes i 132 ^ q 70 138 145 ^ s t 136 ^ u 140 v 150 leaf nodes Database by Yang-Sae Moon

B-트리에서 삽입의 예 (4/4) 인덱스 개념과 종류 반복된 삽입에 따라서 한 레벨이 증가된 B-트리 ao · 69 · a’ 43 · · b’ 19 d · j 7 · · e 16 · k 15 33 · 18 · e’ 26 l 20 · m 22 30 · m’ 36 · · 58 n 41 o 42 50 · · f’ 54 · c f 40 128 · · o’’ 57 o’ 59 g 60 · · p 62 Page 24 70 120 · 100 · 75 110 h 123 · r 138 · g’ q 65 c’ · · · i 132 · 145 · · r’ r’’ s t u v 122 124 130 136 140 150 Database by Yang-Sae Moon

B-트리에서 삭제의 예 (2/3) 인덱스 개념과 종류 b b e l 20 · 26 · 40 · m 30 36 e n 42 l 26 · 30 · 40 · m 36 n 42 노드 l에서 키 값 20의 삭제 Page 27 Database by Yang-Sae Moon

B-트리에서 삭제의 예 (3/3) a b j 15 · 19 · 43 · f e d · 30 · 40 · · 16 · k 18 l 26 m 36 · 30 · · e d · 40 · · 19 · 16 18 d jk 43 f l m n 26 36 42 a b n 42 a b jk 인덱스 개념과 종류 · · 19 · 43 · e · 30 · 40 · 16 18 l 26 m 36 f n 42 노드 j에서 키 값 15의 삭제 Page 28 Database by Yang-Sae Moon

B+-Tree Index Files A B+-tree is a rooted tree that takes the form of a balanced tree in which every path from the root to a leaf is of the same length. root node internal node leaf node data block (records) Indexing and Hashing (Database System Concepts) • If the root is an internal node, it has at least 2 children. • If the root is a leaf, it can have between 0 and (n-1) values. Each node has between n/2 and n children. Each node has between (n-1)/2 and n-1 values. The block stores records. 32

B+-Tree Node Structure Typical node of a B+-tree P 1 K 1 P 2 Pn-1 Kn-1 Pn Ki are the search key values. Pi are pointers. § In case of internal nodes (non-leaf nodes), they point children nodes. § In case of leaf nodes, they point records. The search keys in a node are ordered K 1 < K 2 < K 3 <. . < Kn-1 Indexing and Hashing (Database System Concepts) 33

Leaf Nodes in B+-Trees Properties of a leaf node: For i = 1, 2, …, n-1, Pi points to a file record with search key value Ki. If Li, Lj are leaf nodes and i < j, Li’s search key values are less than Li’s search key values. Pn points to next leaf node in search key order. Brighton Downtown next leaf node account table leaf node Brighton 217 Green 750 Downtown 101 Johnson 500 Downtown 110 Peterson 600 Indexing and Hashing (Database System Concepts) 34

Internal Nodes in B+-Trees Internal nodes form a multi-level sparse index on the leaf nodes. For an internal node with m pointers: P 1 K 1 P 2 … Ki-1 Pi Ki … Km-1 Pm … X X < K 1 Ki-1 X < Ki X Km-1 Indexing and Hashing (Database System Concepts) 35

Example of a B+-Tree (1/2) B+-tree for account file (n = 3) Perryridge Redwood Mianus Brighton Downtown Mianus Perryridge Perryridge Redwood Round Hill Indexing and Hashing (Database System Concepts) Redwood 217 101 110 215 102 201 218 222 305 Green Johnson Peterson Smith Hayes Williams Lyle Lindsay Turner Round Hill 750 500 600 700 400 900 700 350 36

Example of a B+-Tree (2/2) B+-tree for account file (n = 5) Perryridge Brighton Downtown Mianus Perryridge Redwood Round Hill Leaf nodes must have between 2 and 4 values ( (n-1)/2 and n-1, with n = 5). Internal nodes other than root must have between 3 and 5 children ( n/2 and n, with n = 5). Indexing and Hashing (Database System Concepts) 37

Observations about B+-Trees Searches can be conducted efficiently since the B+-tree contains a relatively small number of levels. Set of leaf nodes forms a simple relation having the search key as an attribute. Indexing and Hashing (Database System Concepts) 38

B-tree Index: Revisited (1/2) Similar to B+-tree, but B-tree allows search key values to appear only once; eliminates redundant storage of search keys. Search keys in internal nodes appear nowhere else in the B-tree; an internal node includes an additional pointer field for each search key. Downtown Brighton Clearview Redwood Mianus Brighton Clearview Downtown Mianus Perryridge Redwood Round Hill Indexing and Hashing (Database System Concepts) Perryridge 217 225 101 110 215 102 201 222 305 Round Hill Green George Johnson Peterson Smith Hayes Williams Lindsay Turner 750 850 500 600 700 400 900 700 350 39

B-tree Index: Revisited (2/2) Advantages of B-tree indexes Disadvantages of B-tree indexes l May use less tree nodes than a corresponding B+-tree. l Only small fraction of all search key values are found early. l Sometimes possible to find search key value before reaching leaf node. l Since fan-out of internal nodes is reduced, depth is higher than B+-tree. . l Insertion and deletion more complicated than in B+-trees. l Implementation is harder than B+-trees. The advantages of B-tress are marginal for large indexes. The structural simplicity of a B+-tree is practically preferred. Indexing and Hashing (Database System Concepts) 40

k-d 트리 예제 인덱스 개념과 종류 b (2, 7) (10, 10) : x a (5, 4) c (9, 5) j b : y f c d (3, 1) j (4, 8) e (7, 2) g f (8, 7) : x a h g (1, 4) h (4, 3) i (8, 2) : y e i d (0, 0) Page 44 Database by Yang-Sae Moon

R-트리 (3/6) 인덱스 개념과 종류 MBR 구성의 예 y r 12 r 13 r 14 r 2 r 10 r 6 r 4 r 9 r 5 r 7 r 8 r 11 r 3 x Page 51 Database by Yang-Sae Moon

R-트리 (4/6) 인덱스 개념과 종류 R-트리에 의한 공간 분할의 예 R 1 R 3 r 1 R 2 r 13 R 5 r 12 r 14 r 2 r 10 r 6 r 4 R 6 r 5 r 7 r 8 Page 52 r 11 r 9 r 3 R 7 Database by Yang-Sae Moon

R-트리 (5/6) 인덱스 개념과 종류 R-트리 노드 구성 • 내부 노드 = <(mbr 1, pointer 1), (mbr 2, pointer 2), …> • 단말 노드 = <(mbr 1, object 1), (mbr 2, object 2), …> 노드 a R 1 R 2 노드 b 노드 c R 3 R 4 노드 d r 13 r 14 R 5 R 6 R 7 노드 e 노드 f r 4 r 2 r 10 r 12 r 6 Page 53 노드 g r 5 r 7 노드 h r 8 r 3 r 9 r 11 Database by Yang-Sae Moon