As we can see, we have many more I/O requests when accessing the table than when accessing the index. Here we need to understand how the buffer cache works. All records in our example must first be searched for in the buffer cache. If the data block containing the record is not in the buffer cache, it must be read from the disk. This usually involves overwriting another record in the buffer cache. The records that have the lowest hit count are overwritten. The hit count is increased each time a data block is needed. The size of the affected segments obviously plays an important role in our example. The index albania telegram screening has 3,075 leaf blocks and the table has 78,954 blocks. Each segment is accessed 175,000 times (see Fig. 1). For the index, this means 57 accesses per leaf block. For the table, this means about two hits per block. Therefore, the hit count for the index blocks is naturally higher. This in turn means that the index blocks are more likely to be found in main memory than the table blocks.
Since the index is also sorted according to the search criteria, it can also be assumed that not the entire index is retrieved, but only the part that is relevant for the search. Therefore, it is normally expected that most of the 175,000 accesses to the index can be found via the buffer cache. In comparison, the table access triggers more physical I/O. Adding an additional filter column in the index reduces the total number of physical reads because I/O unfavorable table accesses are avoided by I/O favorable index accesses.
A slow query
A few years ago I was asked to optimize a query that had been keeping me busy for months. Of course, I didn't work on it all the time, but every now and then the problem would pop up in my mind and I would think again about how to solve it.