All Entries in Index are stored as sorted either in ascending or descending order.
If we need MIN or MAX for any column being indexed, its should be easier for oracle to fetch same as both would be at index end points.
Lets understand it better and how it is access to end point within index is implemented with an example.
CREATE TABLE IDX_MIN_MAX
SELECT ROWNUM RN , 'EATSPACE' COL1 FROM DUAL CONNECT BY ROWNUM < 1000000;
CREATE INDEX IDX_RN ON IDX_MIN_MAX(RN);
We have an index created on RN column, let now query to get MIN or MAX in same statement and check underlying execution plan.
SELECT MIN(RN),MAX(RN) FROM IDX_MIN_MAX;
Table full scan is perform to read MIN and MAX data and total memory buffer access to get desired data is 2628.
As we are querying MIN and MAX in same statement, oracle is not able to utilize Index data though it is sorted and required data that are at Extreme ends.
If we need Extreme end point for any indexed columns i.e. MIN and MAX for a indexed column, querying it within same SQL statement scope will impact performance.
Oracle provide “INDEX FULL SCAN (MIN/MAX)” to read extreme end point of indexed data.
It only read extreme ends point of index either MIN or MAX and procure desired result for us.
Lets Rewrite above statement with segregation of MIN and MAX in two different SQL statement scope.
SET AUTOTRACE ON;
SELECT (SELECT MIN(RN) FROM IDX_MIN_MAX) MIN_RN ,
(SELECT MAX(RN) FROM IDX_MIN_MAX) MAX_RN FROM DUAL;
we are querying MIN and MAX in two different SQL statement scope.
lets check underlying execution plan for same.
Elapsed time for SQL execution is minimal and total memory buffer access to get desired output reduced to only 3.
If we need indexed column extreme end points, query both in different SQL statement scope to take advantage of INDEX FULL SCAN (MIN/MAX) access path.
Also if we frequently querying MIN or MAX for a column then having an index on same would give good performance gain.