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:
Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and laterInformation in this document applies to any platform.
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
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
Symptoms |
Changes |
Cause |
Solution |
References |