Having investigated it, I identified the root cause of that issue and constructed a simple test case that can be used to reproduce it.
Let's setup some sample data - a list-partitioned table with just two rows and a global index:
SQL> create table t (
2 id,
3 part_key,
4 constraint t_pk primary key(id))
5 partition by list(part_key) (
6 partition values ('KEY1'),
7 partition values ('KEY2'))
8 as
9 select 1, 'KEY1' from dual union all
10 select 2, 'KEY2' from dual;
Table created.
SQL>
SQL> col part_key for a8
SQL> select *
2 from t;
ID PART_KEY
---------- --------
1 KEY1
2 KEY2
Here are queries returning wrong results:SQL> select max(id)
2 from t
3 where part_key = 'KEY1';
MAX(ID)
----------
2
SQL> select min(id)
2 from t
3 where part_key = 'KEY2';
MIN(ID)
----------
1
They should have returned 1 and 2 correspondingly. Things definitely went awry.In such a scenario, I always try to figure out how exactly the optimizer comes up with the result.
The execution plan is a good place to start:
SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 2094033419 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 19 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| T_PK | 1 | 19 | | | ----------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 13 rows selected.Aha! There is no way for the optimizer to answer that query by using that execution plan.
Where is the PART_KEY predicate? It seems to have been completely lost there.
A quick search on MOS got me a candidate issue: Bug 22913528 - wrong results with partition pruning and min/max scans (Doc ID 22913528.8)
That document mentions a workaround which I successfully implemented:
SQL> select /*+ opt_param('_fix_control' '16346018:0')*/
2 max(id)
3 from t
4 where part_key = 'KEY1';
MAX(ID)
----------
1
SQL>
The plan of that query is below:SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 2831600127 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 19 | 2 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 19 | | | | | | 2 | PARTITION LIST SINGLE| | 1 | 19 | 2 (0)| 00:00:01 | 1 | 1 | | 3 | TABLE ACCESS FULL | T | 1 | 19 | 2 (0)| 00:00:01 | 1 | 1 | ----------------------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 14 rows selected.The MOS document says that this issue has been fixed in the 12.1.0.2.190115 (Jan 2019) Database Proactive Bundle Patch.