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

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
  • Date: Mon, 5 Feb 2018 17:12:49 +0000

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




-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: