Wrong Results With List Partition From 12.1.0.2 Onwards (Doc ID 2491617.1)

Last updated on JULY 20, 2024

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.