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.

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-l
Received on Mon Feb 05 2018 - 17:58:27 CET

Original text of this message