Re: Interesting wrong-results bug 12.1.0.2 - not sure if it's Oracle bug or a bug in our understanding of table partitioning with non-partitioned indexes
Date: Mon, 5 Feb 2018 17:34:51 +0000
Message-ID: <MM1P12301MB1658E77EACA460B76E4625E2A5FE0_at_MM1P12301MB1658.GBRP123.PROD.OUTLOOK.COM>
If it's the wrong result then it's a bug (and you've had two other people suggest the relevant bug number and temporary fix). In principle, however, the index full scan (min/max) could be a valid path for that query.
The table is list partitioned by contract_id (at least, that's the indication from the second plan), so if 12345 is the only contract_id in the list defining its partition the optimizer could have identified the relevant partition data_object_id from the predicate and partition definition and then walked the global index in descending order from the maximum value until it hit the first rowid (which, for global indexes, include the data_object_id) until it hits the right data_object_id.
(I don't think the code to take that approach is in the optimizer, though).
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Chris Taylor <christopherdtaylor1994_at_gmail.com> Sent: 05 February 2018 16:58:27
To: ORACLE-L
Subject: Interesting wrong-results bug 12.1.0.2 - not sure if it's Oracle bug or a bug in our understanding of table partitioning with non-partitioned indexes
Table CLAIM is partitioned by CLAIM_ID and is hundreds of gigabytes.
Primary Key on CLAIM is NOT partitioned (CLAIM_PK)
Query:
select
max(c.claim_id)
from claim c
where c.contract_id = 12345
/
| Id | Operation | Name | E-Rows |E-Bytes| Cost |
| 0 | SELECT STATEMENT | | | | 1899 |
| 1 | SORT AGGREGATE | | 1 | 13 | |
| 2 | INDEX FULL SCAN (MIN/MAX)| CLAIM_PK | 291K| 3703K| |
The problem here is that CONTRACT_ID is NOT on that index - so we get an invalid max(claim_id). There should be an additional table access/partition access here to filter by the contract_id but there isn't.
If we do:
select
max(c.claim_id), max(contract_id)
from claim c
where c.contract_id = 11646
/
Then we get the correct answer Max CLAIM_ID for the CONTRACT we're looking for.
The plan looks like this:
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 1 | 13 | 1899 (1)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION LIST SINGLE| | 291K| 3703K| 1899 (1)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS FULL | CLAIM | 291K| 3703K| 1899 (1)| 00:00:01 | 4023 | 4023 |
So, I'm curious if this is an Oracle bug or our bug? (I think it's an Oracle bug but wanted to make sure I wasn't overlooking something)
Chris
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 05 2018 - 18:34:51 CET