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 KEY2Here 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) ---------- 1They 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.