Wrong Results With List Partition From 12.1.0.2 Onwards
(Doc ID 2491617.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.
Symptoms
- Wrong results with List Partitioning and Dynamic Partition Pruning from 12.1.0.2 onwards
- Wrong results with Automatic List Partitioning from 12.2 as well
- Plan changes between correct and wrong results execution
- Setting optimizer_features_enable to 12.1.0.2 from 12.2 database environment does not help
- Setting optimizer_features_enable to 12.1.0.1 or 11.2.0.4 only helps in giving correct results
- Optimizer performs dynamic partition pruning [AND Pruning (KEY (AP)] for list partitioned table and returns wrong results
- The following is example of wrong results plan:
SQL> select column_1, count(*) from part_table where column_2 = 'value1' and column_1 != value2 group by column_1;
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1442 (1)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 37 | 1442 (1)| 00:00:01 | | |
| 2 | PARTITION LIST AND| | 1 | 37 | 1441 (1)| 00:00:01 |KEY(AP)|KEY(AP)|
|* 3 | TABLE ACCESS FULL| PART_TABLE | 1 | 37 | 1441 (1)| 00:00:01 |KEY(AP)|KEY(AP)|
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("COLUMN_2"='VALUE1' AND "COLUMN_1"<>VALUE2)
Changes
- Upgrading the database to 12.1.0.2 or above.
- List partitioning(12.1) or Automatic List Partitioning (12.2) is used.
- Automatic list partition is used on multiple key columns as below.
Ex: PARTITION BY LIST ("COLUMN_1","COLUMN_2") AUTOMATIC.
Cause
|
To view full details, sign in with your My Oracle Support account. |
| Don't have a My Oracle Support account? Click to get started! |
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.