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

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