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 JULY 20, 2024

Applies to:

Goal

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>;

Oracle 19c:

Now the following questions arise.

1. Which behavior is the correct one?
2. Is the behavior documented somewhere?

Solution

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