MIN / MAX Quiz Answer (One Shot) August 31, 2011
Posted by Richard Foote in CBO, Index Full Scan (Min/Max), MAX, MIN, Oracle Indexes.trackback
Not only are my regular blog readers a good deal better looking than the average person, but they’re quite a bit smarter as well 🙂
As most people have correctly identified, the answer I was after to my previous Min/Max Quiz is that Option 1 is indeed the odd one out, as it’s the only option that can’t make use of the Index Full Scan (Min/Max) access path.
If you’re after either the minimum or the maximum of a column value and the column is indexed, the CBO can potentially use the Index Full Scan (Min/Max), which simply navigates to the first OR last leaf block in the index structure looking for the Min or Max value in question. Oracle can of course navigate to the first (left-most) or last (right-most) leaf blocks very easily by simply following the associated first/last pointers in the Root/Branch structure of the index. All things being equal and providing there haven’t been any subsequent deletes to empty out the index entries from these leaf blocks, Oracle can very quickly determine the minimum or maximum value of the column.
However, the Index Full Scan (Min/Max) can only visit one side of the index, not both. Therefore, if you want both the minimum and the maximum column value, an Index Full Scan (Min/Max) is not viable and the CBO is forced to look for other alternatives. It sounds like such a trivial thing to implement but that’s how it goes. I do remember way back when Oracle9i was released and the introduction of the Index Skip Scan I thought perhaps Oracle might also soon introduce an index skip scan version of Min/Max (as it basically just needs to “skip” all the index leaf blocks in the “middle” of the index via another lookup of the index), but it was not to be.
So for a query such as in Option 1, if the column IS NULL and does not have a NOT NULL constraint, then:
SQL> select min(id), max(id) from muse; MIN(ID) MAX(ID) ---------- ---------- 1 1000000 Execution Plan ---------------------------------------------------------- Plan hash value: 421245806 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 2125 (1)| 00:00:26 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | TABLE ACCESS FULL| MUSE | 1000K| 4882K| 2125 (1)| 00:00:26 | --------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7795 consistent gets 7788 physical reads 0 redo size 470 bytes sent via SQL*Net to client 395 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Then an (expensive) Full Table Scan is likely the way to go. However, if the column has a NOT NULL constraint and the index is indeed smaller than the parent table, then:
SQL> alter table muse modify id not null; Table altered. SQL> select min(id), max(id) from muse; MIN(ID) MAX(ID) ---------- ---------- 1 1000000 Execution Plan ---------------------------------------------------------- Plan hash value: 1592024618 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 611 (1)| 00:00:08 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FAST FULL SCAN| MUSE_ID_I | 1000K| 4882K| 611 (1)| 00:00:08 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2242 consistent gets 0 physical reads 0 redo size 470 bytes sent via SQL*Net to client 395 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Then an Index Fast Full Scan becomes viable.
All the other options I’ve used to return the Min/Max of the column all incorporate two separate SELECT clauses and so all can potentially use an Index Full Scan (Min/Max) access path for each distinct clause.
Be it when using a UNION operation:
SQL> select min(id) as "MIN(ID)/MAX(ID)" from muse union all select max(id) from muse; MIN(ID)/MAX(ID) --------------- 1 1000000 Execution Plan ---------------------------------------------------------- Plan hash value: 1370940131 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 10 | 6 (50)| 00:00:01 | | 1 | UNION-ALL | | | | | | | 2 | SORT AGGREGATE | | 1 | 5 | | | | 3 | INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I | 1 | 5 | 3 (0)| 00:00:01 | | 4 | SORT AGGREGATE | | 1 | 5 | | | | 5 | INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I | 1 | 5 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 456 bytes sent via SQL*Net to client 395 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed
Although as pointed out in the comments, this does return 2 rows.
Or I could use Scalar Sub-Queries:
SQL> select (select min(id) from muse) "MIN(ID)", (select max(id) from muse) "MAX(ID)" from dual; MIN(ID) MAX(ID) ---------- ---------- 1 1000000 Execution Plan ---------------------------------------------------------- Plan hash value: 2177063930 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I | 1 | 5 | 3 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | 5 | | | | 4 | INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I | 1 | 5 | 3 (0)| 00:00:01 | | 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 468 bytes sent via SQL*Net to client 395 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Or indeed I could use a WITH clause:
SQL> with min_id as (select min(id) from muse), max_id as (select max(id) from muse) select * from min_id, max_id; MIN(ID) MAX(ID) ---------- ---------- 1 1000000 Execution Plan ---------------------------------------------------------- Plan hash value: 3280440773 ------------------------------------------------------------------------------------------ | 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)| MUSE_ID_I | 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)| MUSE_ID_I | 1 | 5 | 3 (0)|00:00:01 | ------------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 470 bytes sent via SQL*Net to client 395 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
They’re all subtly different but they all can make use of the Index Full Scan (Min/Max) scan for each separate SELECT clause and they all can perform the necessary resultant work with just 6 consistent gets in my example.
More quizzes to come …
HI Richard,
The output of SQL> select min(id) as “MIN(ID)/MAX(ID)” from muse union all select max(id) from muse; does not match the query.
Lars
LikeLike
Yes thanks Lars, my cut ‘n’ paste didn’t quite work there. Fixed.
LikeLike
Hi Richard,
a bitmap index would show the same behaviour (except for the additional and the fact that the not null constraint would make no difference), I guess. Ok, I don’t guess, but tested it.
Regards
Martin
LikeLike
Hi Richard,
I agree, but I am getting the IFFS, but with a higher cost. Why not an FTS?
Execution Plan
———————————————————-
SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=4498 Card=1 Bytes=14)
1 SORT AGGREGATE (Card=1 Bytes=14)
2 1 INDEX FAST FULL SCAN MUSE_PK (Cost=4498 Card=4 M Bytes=57 M)
Andy
LikeLike
Let’s say that I belong to the Anti-MIN/MAX league, I could still obtain my results using this code.
The rownum=1 is recognized as a stop key, which is perfect to minimize the gets.
Sidenote: One must note forget to add the ‘Order by’ clause in case the index is invalid or the hint is false : Wihtout the ‘order by’ the result would be false as the first row to come would be returned.
LikeLike
Arf: read in my previous comment
INDEX_DESC(a MUSE_ID_I)
and
INDEX_ASC(a MUSE_ID_I)
LikeLike
Dear Richard
I was trying to access index skip scan demo ,but i was told that i should be the user of this blog , and im confused how to become a user of this blog …kindly guide me
regards
vamshi
LikeLike
Hi Kondavk
Fixed the index skip scan demo link so you should now be able to access it.
LikeLike
Hi Richard,
Can you explain why Oracle does SORT AGGREGATE while reading either left most or right most leaf block?
Thanks
LikeLike
with
v_max as
( select * from (
select /*+ INDEX_DESC(a MUSE) */ id as id_max
from muse a order by id desc
) where rownum=1
),
v_min as
( select * from (
select /*+ INDEX_ASC(a MUSE) */ id as id_min
from muse a order by id
) where rownum=1
)
select id_max, id_min from v_max, v_min
/
will this really work if the index is not a primary key and is partitioned?
LikeLike
Hi Ayush
Have you tried it 🙂
LikeLike
[…] I’ve discussed a number of times previously, Oracle can very efficiently use an index to determine either the Min or Max value of a column, by […]
LikeLike