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
From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Mon, 5 Feb 2018 10:58:27 -0600
Message-ID: <CAP79kiTs6NN6TcZS2Y0BNzvFT7f1-fqkcz5VQmg8vZXpLzJoww_at_mail.gmail.com>
Table CLAIM is partitioned by CLAIM_ID and is hundreds of gigabytes.
| 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| |
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)|
E-Time | Pstart| Pstop |
| 0 | SELECT STATEMENT | | 1 | 13 | 1899 (1)|
| 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 |
Date: Mon, 5 Feb 2018 10:58:27 -0600
Message-ID: <CAP79kiTs6NN6TcZS2Y0BNzvFT7f1-fqkcz5VQmg8vZXpLzJoww_at_mail.gmail.com>
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 - 17:58:27 CET