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

Thanks guys - I'll check it out.

Regards,
Chris

On Mon, Feb 5, 2018 at 11:12 AM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> 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_at_gmail.com> 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
>>
>>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

--
http://www.freelists.org/webpage/oracle-l