How to increase performance of SELECT min(),max()
Sometimes I needed to select min and max values in one SQL statement. I usually did:
select min(<column>),max(<column>) from table;
Recently I have found more optimal (in terms of performance and resource usage) way:
select (select min(<column>) from <table>), (select max(<column>) from <table>) from dual; -- Oracle syntax
Here is proof:
If both aggregate functions - min and max - are used together in select clause optimizer decided to do full scan of the table. Even though there is unique index on object_id column.
On the other hand if min and max calculations are split into separate queries optimizer decided to do full scan only unique index.
As you can observe double full index scan costs less then full scan of a table: cost 6 versus cost 117. It results in better performance of an application: user waits less amount of time for results, application is more responsive, less database resources are used.
This behavior regards other aggregate functions too. Like avg(), median(), sum().
Test environment: Oracle 12c,
create table exempobj as select * from dba_objects;
create index uioid on exempobj(object_id);
Keywords: #oracle #oracledatabase #oracledba #postgres #postgresql #mysql #database #developer #development #programming #programmer #programmers #softwaredeveloper #softwareengineering #sql