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.
|
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 ?
|
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:
|
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:
|
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:
|
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:
|
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?
|
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.