Another way an index can be used to access a piece of data: Index Full Scan (Min/Max)
If I’m only interested in the minimal or the maximum value of a column, and there is an index on that column, Oracle can use the Index Full Scan (Min/Max). This access-method starts at (ofcourse) the root-block of the index and then decends to the leftmost leaf-block (for a minimal value) or the rightmost leaf-block (for a maximal value), retrieves the rowid for the smallest (leftmost) or largest (rightmost) value. And that’s it. It doen not even have to visit the table-blocks, because the value (of the column) is also stored in the index.
What does this look like in action? Well I use the same data-setup I used the last few times. Let’s have a look at some explain plans:
select min(n) from index_demo PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- Plan hash value: 3365227721 --------------------------------------------------------------------------------------------- | 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)| INDEX_DEMO_U_N | 1 | 5 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- 9 rows selected.
As you can see the Index Full Scan (Min/Max) is used. Cost is low. Oracle only has to decend the leftmost path of the index. BLEVEL=1 (see data-setup). So 1 root/branch block + 1 leaf block = 2 blocks IO.
That’s it.
Let’s see if that holds true for max(n)
select max(n) from index_demo PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- Plan hash value: 3365227721 --------------------------------------------------------------------------------------------- | 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)| INDEX_DEMO_U_N | 1 | 5 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- 9 rows selected.
Yes…that works. Ofcourse you can not see it in the explain plan but now Oracle should decend by the rightmost branch-blocks.
OK….a little more advanced: A query with min(n) and max(n)
select min(n), max(n) from index_demo PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- Plan hash value: 3091733289 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 168 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | TABLE ACCESS FULL| INDEX_DEMO | 100K| 488K| 168 (1)| 00:00:01 | --------------------------------------------------------------------------------- 9 rows selected.
Ouch….that hurts….Oracle will not first go left, and then go right. That would be 4 blocks IO. NO…Oracle decides that it now should perform a Full Table Scan….pain….everywhere I feel pain.
The index on column n does allow for nulls in the data. Let’s change that
alter table index_demo modify n not null Table altered.
And try again
select min(n), max(n) from index_demo Explained. PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- Plan hash value: 1609178537 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 58 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FAST FULL SCAN| INDEX_DEMO_U_N | 100K| 488K| 58 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- 9 rows selected.
Well…it’s somewhat better. Oracle uses the Index Fast Full Scan. That is faster then the FTS (58 vs. 168), but not nearly as fast as what I expected. Now let me first enable NULL values again.
alter table index_demo modify n null Table altered.
I came up with some alternatives for getting the min(n) and max(n) in one statement. Have a look at the results:
Union all
select min(n) from index_demo union all select max(n) from index_demo PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------- Plan hash value: 3364011568 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 10 | 4 (0)| 00:00:01 | | 1 | UNION-ALL | | | | | | | 2 | SORT AGGREGATE | | 1 | 5 | | | | 3 | INDEX FULL SCAN (MIN/MAX)| INDEX_DEMO_U_N | 1 | 5 | 2 (0)| 00:00:01 | | 4 | SORT AGGREGATE | | 1 | 5 | | | | 5 | INDEX FULL SCAN (MIN/MAX)| INDEX_DEMO_U_N | 1 | 5 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- 12 rows selected.
I believe these are called Scalar Sub Querys:
select (
select min(n)
from index_demo
) min,
(
select max(n)
from index_demo
) max
from dual
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 218373315
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| INDEX_DEMO_U_N | 1 | 5 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| INDEX_DEMO_U_N | 1 | 5 | 2 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
12 rows selected.
Joining them as inline views
select min.min,
max.max
from (
select min(n) min
from index_demo
) min,
(
select max(n) max
from index_demo
) max
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 2289891000
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 |
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| INDEX_DEMO_U_N | 1 | 5 | 2 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | SORT AGGREGATE | | 1 | 5 | | |
| 7 | INDEX FULL SCAN (MIN/MAX)| INDEX_DEMO_U_N | 1 | 5 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
14 rows selected.
You see…there are ways around the problem….but to me these are workarounds. I would like Oracle to solve it in the engine 🙂