If you want to find out the minimum or the maximum of a column value and the column is indexed, Oracle can very quickly determine the minimum or maximum value of the column by navigating to the first (left-most) or last (right-most) leaf blocks in the index structure to get the Min or Max values respectively. This access path known as Index Full Scan (Min/Max) is extremely cost effective as instead of scanning the entire index / table, only first or last entries in the index need to be read.
In case the Select clause includes another column with a function applied to it, optimizer employs Full table Scan instead. In this post, I will demonstrate this scenario and also the solution to the same.
In my test setup, I have a table HR.EMP having index on SALARY column.
— Let’s first query the MIN(SALARY) and SYSDATE from HR.EMP. It can be seen that optimizer employs INDEX FULL SCAN (MIN/MAX) as desired.
SQL>select min(salary), sysdate from hr.emp ;
select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 7c3q3s8g2ucxx, child number 0
-------------------------------------
select min(salary), sysdate from hr.emp
Plan hash value: 3077585419
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_SAL | 107 | 428 | |
----------------------------------------------------------------------
— Now if I try to find out MIN(SALARY) with function applied to SYSDATE, the optimizer chooses costly TABLE ACCESS FULL instead of INDEX FULL SCAN (MIN/MAX) .
SQL>select min(salary), to_char(sysdate, 'dd/mm/yy') from hr.emp ;
select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 3dthda93cgm6v, child number 0
-------------------------------------
select min(salary), to_char(sysdate, 'dd/mm/yy') from hr.emp
Plan hash value: 2083865914
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| EMP | 107 | 428 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------
As a workaround , we can restructure our query as shown so that it uses an inline view to get the MIN(SALARY) so that optimizer chooses INDEX FULL SCAN (MIN/MAX) and function to SYSDATE is applied in the main SELECT clause.
SQL>select min_salary, to_char(sysdt, 'dd/mm/yy') from
(select min(salary) min_salary, sysdate sysdt from hr.emp) ;
select * from table (dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 5rzz6x8wzkh2k, child number 0
-------------------------------------
select min_salary, to_char(sysdt, 'dd/mm/yy') from (select
min(salary) min_salary, sysdate sysdt from hr.emp)
Plan hash value: 2631972856
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | VIEW | | 1 | 19 | 3 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 4 | | |
| 3 | INDEX FULL SCAN (MIN/MAX)| EMP_SAL | 107 | 428 | | |
-------------------------------------------------------------------------
Hope it helps!
References:
AIOUG -North India Chapter- Performance Tuning By Vijay Sehgal – 30th May 2015
Index Full Scan (MIN/MAX) and Partitioned Table
——————————————————————————————————————-
Related links: