My Oracle Support Banner

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 NOVEMBER 13, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 19.8.0.0.0 and later
Information in this document applies to any platform.

Goal

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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

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
Goal
Solution


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