I’ve just noticed an interesting thing:
Assume, that we have a simple query with “MIN(ID)” that works through “Index full scan(MIN/MAX)”:
SQL> explain plan for
2 select
3 min(ID) as x
4 from tab1
5 where ID is not null;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4170136576
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | FIRST ROW | | 1 | 4 | 3 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IX_TAB1 | 1 | 4 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID" IS NOT NULL)
Test tables
create table tab1(id, x, padding)
as
with gen as (select level n from dual connect by level<=1000)
select g1.n, g2.n, rpad(rownum,10,'x')
from gen g1,gen g2;
create index ix_tab1 on tab1(id, x);
exec dbms_stats.gather_table_stats('','TAB1');
[collapse]
But look what will happen if we add one more “MIN(ID)”:
SQL> explain plan for
2 select
3 min(ID) as x
4 , min(ID)+1000 as x1000
5 from tab1
6 where ID is not null;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 3397888171
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3075 (17)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IX_TAB1 | 999K| 3906K| 3075 (17)| 00:00:02 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID" IS NOT NULL)
At first I thought it was the old limitation from “MIN/MAX index access is not used if query has multiple MIN/MAX functions (Doc ID 316467.1)”, but we can get same plan by using hint:
SQL> explain plan for
2 select/*+ index(tab1) */
3 min(ID) as x
4 , min(ID)+1000 as x1000
5 from tab1
6 where ID is not null;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 4170136576
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3433 (22)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | FIRST ROW | | 999K| 3906K| 3433 (22)| 00:00:02 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IX_TAB1 | 999K| 3906K| 3433 (22)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ID" IS NOT NULL)
So, we can see that the real problem lies in the wrong cardinality calculation.
Of course, we can avoid this problem using the old method – with simple inner view:
SQL> explain plan for
2 select x, x+1000
3 from (
4 select
5 min(ID) as x
6 from tab1
7 where ID is not null
8 );
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2347179087
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 13 | 3 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 4 | | |
| 3 | FIRST ROW | | 1 | 4 | 3 (0)| 00:00:01 |
|* 4 | INDEX FULL SCAN (MIN/MAX)| IX_TAB1 | 1 | 4 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID" IS NOT NULL)