Page 105 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

These clustered indexes play a pivotal role in shaping the physical arrangement of data within the table, facilitating
efficient retrieval and storage operations.
Let me demonstrate this with an easy example.

The ‘product’ table will automatically come with a clustered index named ‘product_pkey,’ and this index is structured
around the primary key ‘product_id.’
Now, when you run a query to search the table by ‘product_id’ (like in the query below), the clustered index will
help the database to perform optimal searches, and return the result faster.
SELECT product_name, category, price
FROM product
WHERE product_id = 3;

You might be curious about how it accomplishes this. Indices employ an efficient search technique called binary
search.

Finding Book #75

Binary search stands out as a highly effective algorithm for locating an item within a sorted list. Its methodology
involves iteratively dividing the data in half and assessing whether the target entry, sought through a query, is
positioned before or after the middle entry in the dataset.

If the query value is less than the middle entry, the search narrows down to the lower half; otherwise, it focuses
on the upper half.
This process continues until the desired value is located. The brilliance of binary search lies in its ability to
minimize the number of searches required, resulting in faster query execution.

92

CITS : IT&ITES - Computer software application - Lesson 18 - 36