9.6.8 Performance Optimization for SQL Queries with Minimum or Maximum Functions

SQL queries using minimum or maximum functions are designed to take advantage of the storage index column summary that is cached in Exadata Storage Server memory. As a query is processed, a running minimum value and a running maximum value are tracked. Before issuing an I/O, the minimum/maximum value cached in the storage index for the data region is checked in conjunction with the running minimum/maximum value to decide whether that I/O should be issued or can be pruned. Overall, this optimization can result in significant I/O pruning during the course of a query and improves query performance. An example of a query that benefits from this optimization is:

Select max(Salary) from EMP where Department = 'sales';

Business intelligence tools that get the shape of a table by querying the minimum or maximum value for each column benefit greatly from this optimization.

The following session statistic shows the amount of I/O saved due to storage index optimization.

cell physical IO bytes saved by storage index

Minimum software: Oracle Database release 12.1.0.2.