Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

Min/Max on same column

Mustafa, 2020-05-222020-05-22

Hi,

Let’s do a simple trick. You want to get min and max value of a column which is already indexed. As you know if you have an btree index on a column and try to get a Min or Max value then you will see an “Index Full Scan(Min/Max)” line in the execution plan. since indexes store data in order, it is enough to look for the first or last index leaf to get min or max value.

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create table tmp as select * from dba_objects;
 
create index i_tmp1 on tmp(object_id);
 
exec dbms_Stats.gather_Table_Stats(user, 'TMP', estimate_percent=>100, cascade => true);
 
set autotrace traceonly
 
select max(object_id) from tmp;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3606912980
 
-------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     1 |     5 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |        |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_TMP1 |     1 |     5 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

as you see, we are able to see INDEX FULL SCAN (MIN/MAX) step and our query cost is 2. same thing happens if I replace Max with a Min function.

what if we ask for both, Min and Max ?

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
set autotrace traceonly
 
select max(object_id), min(object_id) from tmp;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3231217655
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   394   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| TMP  | 72492 |   353K|   394   (1)| 00:00:01 |
---------------------------------------------------------------------------

suddenly, Oracle stops using index and change index search with a full table scan. Why? despite of seeing very high cost (cost is 394, it was 2 when it used index) maybe this is because table is so small and Oracle decided to use a full table instead of index scan? let’s check:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
set autotrace off
 
insert into tmp select * from tmp;
insert into tmp select * from tmp;
insert into tmp select * from tmp;
insert into tmp select * from tmp;
commit;
 
alter index i_tmp1 rebuild;
 
exec dbms_Stats.gather_Table_Stats(user, 'TMP', estimate_percent=>100, cascade => true);
 
select count(*) from tmp;
--1.159.872 rows

now we have 1.1 million rows in the table and it is not that small now. I’ve already rebuild the index and gather the statistics so let’s try again:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
set autotrace traceonly
 
select max(object_id) , min(object_id) from tmp;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3231217655
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |  6367   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| TMP  |  1159K|  5663K|  6367   (1)| 00:00:01 |
---------------------------------------------------------------------------

as we can see our table is not that small and even so Oracle uses a full table scan with cost 6367. even in that big table index scan cost does not change much:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select max(object_id) from tmp;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3606912980
 
-------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     1 |     5 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |        |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_TMP1 |     1 |     5 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

just index scan cost is 3. so instead of full table scan, you can select min first and then run max as different 2 queries and your total cost is almost 6. this is much much less than Oracle’s default execution plan. Even using a HINT won’t work because Oracle is not able to do that with both min and max aggregate functions. I didn’t go deeper but as I understand Oracle can use just one directional index search (asc or desc) not both so instead of using an index it decides full table scan operation.

You might think that maybe this is because column contains null values and not marked as a not null and somehow this is effecting the optimizer? yes and no. let’s delete null values and modify column not null:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
set autotrace off
 
delete tmp where object_id is null;
commit;
-- it deletes 32 rows on my dummy db and it won't affect statistics since table has 1.1M rows,
 
alter table tmp modify object_id not null;
 
set autotrace traceonly
select max(object_id) , min(object_id) from tmp;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3112928733
 
--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |     5 |   707   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_TMP1 |  1159K|  5663K|   707   (2)| 00:00:01 |
--------------------------------------------------------------------------------

huh! now it changed and used the index with a cost 707. this is much better than full table scan (6367 cost) but if you pay attention, you will see that this is not “Index Full Scan (Min/Max)“, instead this is “Index Fast Full Scan”. in a Index Full Scan (min/max) Oracle just read first or last leaf almost nothing more and this is more than enough to get what we need but in that case Oracle has read entire index (allleaves) so this cost as a lot! as I said running min and max in a 2 different query will cost us 6 (each of them is 3).

so how can we workaround?

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
set autotrace traceonly
 
with min_data as (
  2      select min(object_id)
  3      from tmp
  4    ),
  5    max_data as (
  6      select max(object_id)
  7      from tmp
  8    )
  9  select *
10  from min_data, max_data;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3456406915
 
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    26 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |        |     1 |    26 |     6   (0)| 00:00:01 |
|   2 |   VIEW                       |        |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |        |     1 |     5 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| I_TMP1 |     1 |     5 |     3   (0)| 00:00:01 |
|   5 |   VIEW                       |        |     1 |    13 |     3   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE            |        |     1 |     5 |            |          |
|   7 |     INDEX FULL SCAN (MIN/MAX)| I_TMP1 |     1 |     5 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
/*************************************************************************/
/****** OR ******/
 
select (select min(object_id) from tmp ),
  2         (select max(object_id) from tmp )
  3  from   dual;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 446856238
 
-------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     1 |       |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |        |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| I_TMP1 |     1 |     5 |     3   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE            |        |     1 |     5 |            |          |
|   4 |   INDEX FULL SCAN (MIN/MAX)| I_TMP1 |     1 |     5 |     3   (0)| 00:00:01 |
|   5 |  FAST DUAL                 |        |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

as you see we are so close to total cost of 6. Optimizer does a lot for us but sometimes we need to help.

Have healthy days, please stay home if you can. stay safe.

11g 12c 18c 19c Performance SQL / PLSQL aggregates on same columnmin and maxmin and max performancemin/max

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Archives

  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
  • Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes