Indexes: Definition, Clustered versus Unclustered indexes

 

Welcome to class! 

In today’s class, we will be talking about indexes: definition, clustered versus unclustered indexes. Enjoy the class!

Indexes

INDEXES: Clustered versus Unclustered indexes | classnotes.ng

Definition

An index is a copy of a database table that has been reduced to certain fields and the copy is always in sorted form. The index also contains a pointer to the corresponding record of the actual table so that the fields not contained in the index can also be read. An index contains a value and a pointer to the first record that contains data value.

A Database Index is a data structure that speeds up certain operation on a file. The Operation involves a search key which is the set of record files (in most cases a single field). The elements of an index are called data entries. Data entries can be actual data record. A given file of data records can have several indexes, each with different search keys as shown in the table below.

Customer ID Name Address City State Zip
001 Mr Daniel 10, Bale str. Maryland Lagos 1011
002 Mrs Okon 5, Oju-ile Ota Ogun 1021
003 Mr David 26, Dalemo str. Ikeja Lagos 1023

The search engine searches for a value in table or file in two ways. The sequential table scan and index which is random.

Indexes are special lookup tables that the database engine uses to speed up data retrieval. An index in a database is similar to an index in the back of a book.

An index table or file consists of records called index entries. It is of the form

Search- key Pointer

The search key field is used to sort the rows (in the index column) and the pointer field (in index column) indicates where the actual data in the table will be retrieved. When a table has an index it simply means the records in that table has been sorted in one way or the other.

Indexes are automatically created when primary key and unique constraints are defined on table columns.

Evaluation

  1. What is an index?
  2. What are data entries?

Index classification

An index can be classified as either clustered or unclustered.

  1. Clustered index:

A Clustered index is when a file is organized so that the ordering of data records is the same as or closes to the ordering of data entries. A clustered index can take place only if the data records are sorted on the search key field. For example, suppose that students records are sorted by age; an index on the age that stores data entries in sorted order by age is a clustered index.

Indexes that maintain data entries in sorted order by search key use a collection of index entries, organized into a tree structure to guide searches for data entries. Thus, clustered indexes are relatively expensive to maintain when the file is updated, when data entries are to be moved across pages, and if records are identified by a combination of page id and slot as is often the case, all places in the database that points to a moved record must also be updated to point to the new location. These additional updates can be time-consuming.

The table below illustrate a clustered index file:

Student ID Name Age
00231364OJ Olu Jacob 12
00241265AF Agu Faith 13
00251057AJ Abiola Joseph 13
00211362MS Mathew Stephen 14
00251302TB TjomasBintu 15
  1. Unclustered index:

This an index whose sorting order does not determine the order of how the rows/records in a table are stored. This means that the search keys in the index column are sorted in one order while the actual records or rows are sorted in another order or are not sorted at all.

This is an index that is not clustered. A data file can contain several unclustered indexes. For example, suppose that students’ records are sorted by age; and if an additional index on GPA field is included, it is called unclustered index.

General evaluation
  1. Explain a database index
  2. Explain clustered index versus unclustered index.

Reading assignment

Understanding Data Processing for Senior Secondary Schools by Dinehin Victoria, Page 254.

Weekend assignment

  1. ………. is a database table that has been reduced to certain fields.     a) Table  b) An index c) Table model d) Network model
  2. The copy of an index is always in  …… form.  a) duplicate b) field c) sorted  d) domain
  3. The …………index can take place only if the data records are sorted on the search key field. a)unclustered b)insert c) update  d) clustered
  4. A …………….. can contain several unclustered indexes a) data file b) primary c) check d) index
  5. An index contains a value and  ……. a)pointer b) sign c) update d) model

Theory

  1. Differentiate between the clustered index and unclustered index.
  2. State two reasons why clustered index is expensive to maintain.
  3. What is an index?

 

In our next class, we will be talking about Indexes: Dense versus Sparse, Primary and Secondary Indexes, Indexes using Composite Search Keys.  We hope you enjoyed the class.

Should you have any further question, feel free to ask in the comment section below and trust us to respond as soon as possible.

For more class notes, homework help, exam practice, download our App HERE

Join ClassNotes.ng Telegram Community for exclusive content and support HERE

Leave a Reply

Your email address will not be published. Required fields are marked *

Don`t copy text!