Its a bug - most probably 2208445.1 although there are a frankly alarming
number of matches against previous versions which *may* be pertinent if
you've set optimizer_features_enable to a previous release
On Mon, Feb 5, 2018 at 4:58 PM, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> wrote:
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