The content is just an excerpt from the complete note for SS2 Third Term Data Processing Lesson Note – Indexes. Check below to download the complete DOCUMENT
WEEK 8
TOPIC: INDEXES
CONTENT:
SUBTOPIC 1:
What is an Index?
Is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. An index in a database works the same way in that a query is pointed to the exact physical location of data in a table.
Types of Index
Clustered vs Non-clustered Indexes
Clustered: is where the table data is physically stored in the order of the keys on the index.
Non-clustered: is where the storage order of data in the table is not related to index keys.
You can create only one clustered index on a table because there is only one possible physical ordering of the data rows. You can create up to 249 non-clustered indexes per table.
Clustered and non-clustered tree indexes are illustrated below
Thus, clustered indexes are relatively expensive to maintain when the file is updated. Another reason clustered index is expensive to maintain is that data entries may have 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 point to a moved record (typically, entries in other indexes for the same collection of records) must also be updated to point to the new location; these additional updates can be very time consuming.
To gain full access to the note: DOWNLOAD FILE
Do you want to study Agricultural Extension in OOU? Before you apply for the OOU…
Do you want to study Agricultural Economics in OOU? Before you apply for the OOU…
Do you want to study Cooperative and Business Management in OOU? Before you apply for…
Do you want to study Home and Hotel Management in OOU? Before you apply for…
Do you want to study Animal Production in OOU? Before you apply for the OOU…
Do you want to study Crop Production in OOU? Before you apply for the OOU…