MIN , MAX and INDEX Access in Oracle.

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
AS
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;

min_max_index_1

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.

min_max_index_2

Elapsed time for SQL execution is minimal and total memory buffer access to get desired output reduced to only 3.

#Learning,
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.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal, Oracle Performance and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s