Database Indexes: The Key to Performance Optimization

[ayshakhatun3113]

Databases are the backbone of modern applications, but as the amount of data increases, challenges arise, especially in terms of query speed. Database indexes, particularly the B-tree index, are the focus of this review to understand how they help speed up database queries.

Database indexes play a crucial role in the world of Oracle databases. As data volumes increase, database users often struggle with slow query speeds. To solve this problem, tables are indexed. However, it is not enough to simply create new indexes to speed up queries. To fully exploit the potential of indexes, a deep understanding of how they work is required. In this article, we take a closer look at how indexes work using the example of Oracle databases.

Objective of database queries
When applying a SELECT query to one or more tables, one of bc data the main goals is to minimize query time. Data is stored in blocks, known as DB pages, on the hard disk. Reading and writing from the hard disk is a time-consuming process. Data that is queried frequently is stored in the limited buffer cache. The more frequently data has to be read from the hard disk, the longer a query takes.

If the data is restricted in the SELECT statement by filter criteria (WHERE condition) and no indexes are available, all data records in this table must be read from the hard disk and checked against the given filter criteria.

Such a full table scan may not be a problem for small tables. With thousands or millions of data records, however, this procedure leads to longer waiting times. Especially if several tables are linked, as is the case with databases normalized in the Star schema.

Indexes are used to reduce wait times and minimize the number of read operations on the hard disk.

The B-tree index
One of the most commonly used index types is the B-tree index. This is a hierarchical tree structure based on a specific set of rules to enable faster and more memory-efficient searches, insertions, and deletions of data. The name "B-Tree" refers to the balanced structure of the tree ("balanced tree"). This means that all leaf nodes are on the same level and the degree of branching is kept relatively low to ensure fast access to leaf nodes. The B-tree index stores data in sorted order, making it very efficient for range queries (WHERE X BETWEEN Y AND Z) and equality checks (WHERE X = Y). B-Tree is the standard index type for several database systems such as Oracle and MySQL. The following example shows a simple B-tree index based on a list of numbers.