Speeding up executions of userdefined functions in SQL


Database & Infrastructure

Date and time

Thursday, 19. May 2022., 14:30


Hall A



We should be careful with mixing SQL and PL/SQL.The context switches between both engines can quickly kill performance.For quite a long time now Oracle supports bulk operations in PL/SQL, minimizing these context switches when executing SQL from PL/SQL.Version 12c of the database finally brought us similar enhancements for the other way around: the use of PL/SQL (function calls) from within a SQL statement.Starting with that edition subquery factoring (the with clause) can also include definitions of PL/SQL-functions and (yes, even) -procedures for use inside your query.And then there is the option to compile functions and procedures in the database/packages for use by the SQL engine, using the udf pragma.This presentation will show both features and how and when to put them to use.It will show what the difference in performance will be, compared to each other and to using conventional function calls.Furthermore it will highlight the pitfalls that can be encountered.

Lecture details

Talk Attendence: Attendance in person
Type: Lecture
Level of difficulty: General
Desirable listeners function: Developers , DBA
Group of activity: Database & Infrastructure

About speaker