Oracle Database Free

Hi, Oracle developers, thanks for reading my report. I find a missed optimization in Oracle.

• Steps to Reproduce

SET TIMING ON;
SET AUTOTRACE ON EXPLAIN;
-- insert into t0 with 100 million rows
CREATE TABLE t0(c0 NUMBER(10));
INSERT INTO t0 (c0) SELECT ROWNUM FROM XMLTABLE('1 to 100000000');
ALTER TABLE t0 ADD CONSTRAINT t0_pk PRIMARY KEY (c0);

-- positive cases
SELECT MAX(c0) FROM t0; -- INDEX FULL SCAN (MIN/MAX), Rows: 1
MAX(C0)
----------
100000000
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 381701280
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| T0_PK | 1 | 13 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------

SELECT MIN(c0) FROM t0; -- INDEX FULL SCAN (MIN/MAX), Rows: 1
MIN(C0)
----------
1
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 381701280
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| T0_PK | 1 | 13 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------

-- negative case
SELECT MAX(c0), MIN(c0) FROM t0;-- TABLE ACCESS FULL, Rows: 109M
MAX(C0) MIN(C0)
---------- ----------
100000000 1
Elapsed: 00:00:03.56
Execution Plan
----------------------------------------------------------
Plan hash value: 3461002123
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 41391 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T0 | 109M| 1357M| 41391 (1)| 00:00:02 |
---------------------------------------------------------------------------

• Expected Result

The query SELECT MAX(c0), MIN(c0) FROM t0 should use the primary key index to retrieve both the minimum and maximum values efficiently, e.g.: Two INDEX FULL SCAN (MIN/MAX) operations (one for MIN, one for MAX); A single index scan that extracts both endpoints (like INDEX FULL SCAN (MIN/MAX) on a B-tree index which can provide both min and max in one pass).

Expected execution time: ~0.02 seconds (similar to individual queries).

• Actual Result

The optimizer chooses a Full Table Scan and aggregates all 100 million rows, resulting in poor performance: Execution time 3.56 seconds.

• Additional Information

I execute these cases in the latest docker of Oracle free database, i.e., container-registry.oracle.com/database/free:latest.

This is a common optimization pattern. Other DBMSs, such as MySQL and MariaDB, can handle such cases, as shown in the following figure.