class: bottom, left
background-image: url(images/g.png)

<h2 class="title_headings_sml">COSC210 Database Management Systems</h2>

<h1 class="title_headings_sml"> Lecture 9 - Indexing Structures for Files</h1>

<h3 class="title_headings_sml"> Dr. Mitchell Welch </h3>


---
background-image: url(images/reg_background.png)
## Reading

* Chapter 17 from ***Fundamentals of Database Systems*** by Elmazri and Navathe

---
background-image: url(images/reg_background.png)
## Summary
* Single Level Indexes
* Multilevel Indexes
* Dynamic Indexes Using B-Trees
* Creating Indexes Within PostgreSQL Tables

---
background-image: url(images/reg_background.png)
## Single Level Indexes

* From Chapter 17 we will now look at ***auxiliary access structures*** called **indexes**.
* Indexes are designed to speed up the retrieval of individual records within the file .
    * Indexes achieve this by providing a ***Secondary Access Path*** based upon an indexing field(s).
    * Indexes can utilise **ordered** (similar to a book index in alphabetical order) or **unordered**.
* For **example**, if we often perform operations on our COMPANY database that involve searching for individual employees based upon their first and last names, we could create an index on these fields that provides direct access based upon the first and last name.

---
background-image: url(images/reg_background.png)
## Single Level Indexes

* Types of ordered indexes include **primary** and **clustering** indexes and **secondary** indexes are a form of unordered indexing.
* A **primary index** is an ordered file whose records are of a fixed length consisting of **two fields**:
   * The first is the **key** value - the primary key
   * The second is the **pointer** to a **disk block** 
* There is **one entry** in the index file for **each block** of data in the data file.
* The **first record** in each data block is referred to as the ***anchor*** record 

---
background-image: url(images/reg_background.png)
## Single Level Indexes

* **Indexes** can be characterised as either ***Dense* or *Sparse***.
    * A **dense** index has an entry for each of the search **key** values (therefore **every record**).
    * A **sparse** index index has entries for only a **subset** of the search values (multiple records per block).
* The **primary index** is an example of a **sparse** index - it only contains an entry for the record at the start of each block. (i.e. the **Anchor record**)

---

background-image: url(images/reg_background.png)
## Single Level Indexes

* In the following example, the first and last name are used as the primary key for the file.
* The entires in the index file contain the key values of the anchor records for each block.


---
background-image: url(images/reg_background.png)
## Single Level Indexes

![center-aligned image](images/lecture_14/prim.png)


---
background-image: url(images/reg_background.png)
## Single Level Indexes

* The **index** occupies **less storage** space than the data file.
    * There are fewer entires as the index only contains a **single record** for **each block** within the data file.
    * The index only has **two fields**, where as the data file may have many fields. 
* **Insertion and deletion** are **expensive** operations.
    * These **operations** can **change** the **anchor** records for the blocks. 
    * This will require updates within the index file.
---
background-image: url(images/reg_background.png)
## Single Level Indexes
* **Clustering indexes** are another example of a **non-dense** index.
* In this situation, the file is ordered based upon a **non-key** field.
* There is only **one entry** in the index file for **each distinct value** from the clustering field the in the data file.
* The index file's **pointer** points **to** the ***first* block** that contain the corresponding index value.
* Retrieving returns all records that correspond to a distinct key.

---
background-image: url(images/reg_background.png)
## Single Level Indexes

![center-aligned image](images/lecture_14/cluster.png)

---
background-image: url(images/reg_background.png)
## Single Level Indexes

* **Secondary** indexes work on **non-ordering** fields within files. 
* The **index file will be ordered** by the index field.
* They provide an additional means to access records within the file. 
* Because the data file is not ordered by the indexing field, we **cannot use** the **block anchors** for accessing data.
    * As a result, there will be an **entry** in the index file **for each record** in the data file.
    * This entry will usually **point** to the ***block* location** within the data file, not the actual record location.
    * This implies **dense** indexing.

---
background-image: url(images/reg_background.png)
## Single Level Indexes


![center-aligned image](images/lecture_14/secondary.png)


---
background-image: url(images/reg_background.png)
## Multilevel Indexes

* **So far** we have only looked at **single level** index files.
   * I.e. there is a single entry that **points directly to** the **block** location within the data file.
* In a multi-level arrangement, there are multiple index files organised in a hierarchy with pointers between them.
* In a **single-level** index, a binary search requires `\(log_2(b_{index})\)` **block accesses** for an index with `\(b_{index}\)` blocks - with each iteration.
* This is efficient because each iteration of the algorithm reduces the search space by a factor of 2.
* A **multilevel** index **reduces** the **search space by** a factor of `\(bfr_{index}\)` by what is called ***fanning* out**.

---
background-image: url(images/reg_background.png)
## Multilevel Indexes

![center-aligned image](images/lecture_14/multi.png)

---
background-image: url(images/reg_background.png)
## Dynamic Indexes Using B-Trees

* A ***Tree***, in this context, is a **data structure** that consists of ***nodes*** that linked together by references.
   * Each **node** has two or more **child nodes**
   * Any node that does not have a child not is a ***leaf* node** 

---
background-image: url(images/reg_background.png)
## Dynamic Indexes Using B-Trees

![center-aligned image](images/lecture_14/tree.png)

---
background-image: url(images/reg_background.png)
## Dynamic Indexes Using B-Trees

* A *Search Tree* is a special type of tree that guides the search for a record based upon a particular field.
* Each leaf node in this structure is a pointer to a particular record in the data file.
* A search tree of *order p* has, at most, *p-1* search values and pointers in the order *< P <sub>1</sub>, K<sub>1</sub>,P<sub>2</sub>, K<sub>2</sub>,P<sub>q-1</sub>, K<sub>q-1</sub>P<sub>q</sub>\>* where *P<sub>i</sub>* is a pointer to a child node.

---
background-image: url(images/reg_background.png)
## Dynamic Indexes Using B-Trees

![center-aligned image](images/lecture_14/stree.png)


---
background-image: url(images/reg_background.png)
## Dynamic Indexes Using B-Trees

* To search for records by a key value, we simply traverse the tree, checking the values in each node as we move through.
* This is applied to a file search by selecting a search field and associating each key value with a pointer to the record in the file.
* Trees can either be balanced or unbalanced. Balanced trees have all of their leaf nodes at the same level.
* Balancing a tree ensures that the search speed is consistent for all key values
 	* This requires re-balancing in the case of deletion and insertion.

---
background-image: url(images/reg_background.png)
## Creating Indexes on PostgreSQL Tables

* Indexes can be created on fields within PostgreSQL using the `CREATE INDEX` clause. With the syntax:

```sql

CREATE INDEX ON table (index_field) USING method;

```
* `CREATE INDEX` constructs an index on the specified column(s) of the specified table. Indexes are primarily used to enhance database performance (though inappropriate use can result in slower performance).

---
background-image: url(images/reg_background.png)
## Creating Indexes on PostgreSQL Tables


* The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses. Multiple fields can be specified if the index method supports multicolumn indexes.
* By default, PostgreSQL uses the b-tree structure to implement its indexes, however other methods are available.
* The *method* option lists the name of the index method to be used. Choices are btree, hash, gist, and gin. The default method is btree.

---
background-image: url(images/reg_background.png)
## Summary
* Single Level Indexes
* Multilevel Indexes
* Dynamic Indexes Using B-Trees
* Creating Indexes Within PostgreSQL Tables

---
background-image: url(images/reg_background.png)
## Reading

* Chapter 17 from ***Fundamentals of Database Systems*** by Elmazri and Navathe


---