SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Chapter 11: Indexing and Hashing

corinthia avatar
corinthia
34 diapositives · 520 Views · Dec 04, 2012

This chapter explores the fundamentals of indexing and hashing in databases. It covers basic concepts, ordered indices, B+-Tree index files, and various hashing techniques (static and dynamic). Key evaluation metrics, such as access time, update time, and space overhead, are discussed alongside access types like point and range queries. Additionally, the chapter delves into conventional indexes, including dense and sparse indexes, and highlights the operational mechanisms of B+-Trees for insertion and deletion. Detailed examples illustrate how to create and drop indexes in SQL.

#knot polynomial #piolet #the #key word #eleventh #index #come #pearson hashing
Chapter 11: Indexing and Hashing - slide 1
01 / 34Chapter 11: Indexing and Hashing
1 / 17

Télécharger la présentation

Chapter 11: Indexing and Hashing
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. Chapter 11: Indexing and Hashing • Indexing • Basic Concepts • Ordered Indices • B+-Tree Index Files • Hashing • Static • Dynamic Hashing

  2. search key pointer Basic Concepts Value • Search Key - set of attributes used to look up records in a file. record ? value

  3. Index Evaluation Metrics • Access types supported efficiently. E.g., • Point query: find “Tom” • Range query: find students whose age is between 20-40 • Access time • Update time • Space overhead

  4. Ordered Indices • In an ordered index, index entries are stored sorted on the search key value. E.g., author catalog in library.

  5. same order 50 90 10 130 170 210 110 70 150 30 190 230 70 90 30 10 50 20 80 60 40 100 Search key • Primary index • Also called clustering index • The search key of a primary index is usually but not necessarily the primary key.

  6. different order 100 80 20 90 30 60 50 70 40 10 10 50 20 60 30 70 40 ... Search key Secondary index: • non-clustering index.

  7. 10 70 50 30 110 90 80 60 40 20 120 100 90 10 70 50 30 80 60 40 20 100 Sequential File Dense Index Dense Index: contains index records for every search-key values.

  8. 10 130 90 50 210 170 150 110 70 30 230 190 90 10 70 50 30 80 60 40 20 100 Sequential File Sparse Index • Sparse Index: contains index records for only some search-key values. • Applicable when records are sequentially ordered on search-key

  9. 100 30 20 80 90 10 40 60 50 70 90 30 ... 20 80 100 does not make sense! Secondary indexes Sequence field • Sparse index

  10. 90 170 10 210 170 130 330 10 490 50 570 30 70 110 410 230 90 250 150 190 90 10 70 30 50 60 100 40 80 20 Multilevel Index Sequential File Sparse 2nd level

  11. 90 30 20 80 100 50 70 40 10 60 10 10 50 50 60 20 70 30 90 40 ... ... sparse high level Multilevel Index Secondary indexes Sequence field • Lowest level is dense • Other levels are sparse

  12. Conventional indexes Advantage: - Simple - Index is sequential file good for scans Disadvantage: - Inserts expensive

  13. Outline • Conventional indexes • B+-Tree  NEXT

  14. NEXT: Another type of index • Give up on sequentiality of index • Try to get “balance”

  15. B+Tree Example n=4 Root 100 120 150 180 30 3 5 11 120 130 180 200 100 101 110 150 156 179 30 35

  16. Sample non-leaf 57 81 95 to keys to keys to keys to keys < 57 57 k<81 81k<95 95 Key is moved (not copied) from lower level non-leaf node to upper level non-leaf node

  17. Sample leaf node: From non-leaf node to next leaf in sequence 57 81 95 To record with key 57 To record with key 81 To record with key 85 Key is copied (not moved) from leaf node to non-leaf node

  18. n=4 Leaf: Non-leaf: 30 35 30 35 30 30

  19. Size of nodes: n pointers n-1 keys

  20. Don’t want nodes to be too empty • Use at least Root : 2 pointers Non-leaf: n/2 pointers Leaf : (n-1)/2 keys

  21. n=4 Full node min. node Non-leaf Leaf 120 150 180 30 3 5 11 30 35 counts even if null

  22. B+tree rules tree of order n (1) All leaves at same lowest level (balanced tree) (2) Pointers in leaves point to records • except for “sequence pointer”

  23. (3) Number of pointers/keys for B+tree Max Max Min Min ptrs keys ptrsdata keys Non-leaf (non-root) n n-1 n/2 n/2- 1 Leaf (non-root) n n-1 (n-1)/2 (n-1)/2 Root n n-1 2 1

  24. Insert into B+tree (a) simple case • space available in leaf (b) leaf overflow (c) non-leaf overflow (d) new root

  25. 32 n=4 100 (a) Insert key = 32 30 3 5 11 30 31

  26. 7 3 5 7 n=4 100 (b) Insert key = 7 30 3 5 11 30 31

  27. 160 180 160 179 n=4 100 (c) Insert key = 160 120 150 180 180 200 150 156 179

  28. 30 new root 40 40 45 n=4 (d) New root, insert 45 10 20 30 1 2 3 10 12 20 25 30 32 40

  29. Deletion from B+tree (a) Simple case - no example (b) Coalesce with neighbor (sibling) (c) Re-distribute keys (d) Cases (b) or (c) at non-leaf

  30. 40 n=5 (b) Coalesce with sibling • Delete 50 10 40 100 10 20 30 40 50

  31. 35 35 n=5 (c) Redistribute keys • Delete 50 10 40 100 10 20 30 35 40 50

  32. new root 40 25 30 (d) Non-leaf coalese • Delete 37 n=5 25 10 20 30 40 25 26 30 37 1 3 10 14 20 22 40 45

  33. B+tree deletions in practice • Often, coalescing is not implemented • Too hard and not worth it!

  34. Index Definition in SQL • Create an index create index <index-name> on <relation-name> (<attribute-list>) E.g.: create index gindex on country(gdp); • To drop an index drop index <index-name> E.g.: drop index gindex;