Partition Pruning Not Work For SQL With Predicate Of User Function After Upgrade To 19c (Doc ID 2717940.1)

Last updated on MARCH 08, 2022

Applies to:

Symptoms

• After upgrade to 19c, partition pruning becomes not work for the SQL with predicates of user defined function, while partition pruning works for the same SQL prior to 19c.
• The problem can be reproduce by following test case:
  
  
  CREATE TABLE <TABLE_NAME> (
       COL1  CHAR(3)
      ,COL2  CHAR(17)
      ,COL3  NUMBER(7,0)
      ,COL4  CHAR(8)
      ,COL5  NUMBER(1,0)
  )
  PARTITION BY RANGE (COL4)
  (
      PARTITION PT_20200901 VALUES LESS THAN ('20200902'),
      PARTITION PT_20200902 VALUES LESS THAN ('20200903'),
      PARTITION PT_20200903 VALUES LESS THAN ('20200904'),
      PARTITION PT_20200904 VALUES LESS THAN ('20200905'),
      PARTITION PT_20200905 VALUES LESS THAN ('20200906'),
      PARTITION PT_20200906 VALUES LESS THAN ('20200907'),
      PARTITION PT_20200907 VALUES LESS THAN ('20200908'),
      PARTITION PT_20200908 VALUES LESS THAN ('20200909'),
      PARTITION PT_20200909 VALUES LESS THAN ('20200910'),
      PARTITION PT_20200910 VALUES LESS THAN ('20200911'),
      PARTITION PT_99999999 VALUES LESS THAN (MAXVALUE)
  );
  
  CREATE or REPLACE FUNCTION <USER_FUNCTION_NAME>(ARG VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN ARG;
  END;
  /
  
  set lines 200
  EXPLAIN PLAN FOR SELECT * FROM <TABLE_NAME> WHERE COL4 <= <USER_FUNCTION_NAME>(:B1) AND COL4 >= <USER_FUNCTION_NAME>(:B2);
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
  
  Test results in 19c:
  
  
  PLAN_TABLE_OUTPUT
  -----------------------------------------------------------------------------------------------------
  Plan hash value: 1909639130
  
  -----------------------------------------------------------------------------------------------------
  | Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  -----------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT    |               |     1 |    60 |     2   (0)| 00:00:01 |       |       |
  |   1 |  PARTITION RANGE ALL|               |     1 |    60 |     2   (0)| 00:00:01 |     1 |    11 |
  |*  2 |   TABLE ACCESS FULL | <TABLE_NAME>  |     1 |    60 |     2   (0)| 00:00:01 |     1 |    11 | <<<<<< All partitions is being accessed
  -----------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  
  ---------------------------------------------------
  
     2 - filter("COL4"<="<USER_FUNCTION_NAME>"(:B1) AND "COL4">="<USER_FUNCTION_NAME>"(:B2))
  
  Note
  -----
     - dynamic statistics used: dynamic sampling (level=2)
  
  18 rows selected.
  
  
  Test results prior to 19c:
  
  
  
  PLAN_TABLE_OUTPUT
  ----------------------------------------------------------------------------------------------------------
  Plan hash value: 1276201164
  
  ----------------------------------------------------------------------------------------------------------
  | Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  ----------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT         |               |     1 |    60 |     2   (0)| 00:00:01 |       |       |
  |   1 |  PARTITION RANGE ITERATOR|               |     1 |    60 |     2   (0)| 00:00:01 |   KEY |   KEY |
  |*  2 |   TABLE ACCESS FULL      | <TABLE_NAME>  |     1 |    60 |     2   (0)| 00:00:01 |   KEY |   KEY | <<<<<< Dynamic partition pruning
  ----------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  
  ---------------------------------------------------
  
     2 - filter("COL4"<="<USER_FUNCTION_NAME>"(:B1) AND "COL4">="<USER_FUNCTION_NAME>"(:B2))
  
  Note
  -----
     - dynamic sampling used for this statement (level=2)
  
  18 rows selected.

Changes

 Upgrade to 19c.

Cause

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