PL/SQL Function Called For Each Row on Partitioned Table In 19c As Opposed To Only Once In 18c
(Doc ID 2728655.1)
Last updated on APRIL 17, 2023
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52.0 and later
Information in this document applies to any platform.
If we query a partitioned table and the value for the partition key is the result of a PL/SQL function (which does not have a dependence on the table), e.g.
select * from <partitioned_table_name> where <partition_key_col>= <func_name>(...);
in Oracle 19c, the function "<func_name>" is called once for each row in ALL partitions. In contrast, in Oracle 18c, the function is called only once.
Since in Oracle 19c it is apparently assumed that during the execution of the query the function can return different values if called more than once, all partitions are read instead of only one, as in Oracle 18c.
This behavior is observed on all partitioning types.
Example for HASH-partitioned table:
select * from <partitioned_table_name> where <partition_key_col> = <func_name>;
Now the following questions arise.
- Which behavior is the correct one?
- Is the behavior documented somewhere?
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