Erik van Roon

Speeding up executions of userdefined functions in SQL

Erik van Roon

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.

Informacije o predavanju

Jezik / Language: ENG

O Avtorju

Erik van Roon is an Oracle Database Developer and Oracle ACE who has worked with Oracle technology since 1995, specializing in, but not limiting to, SQL and PLSQL. Since 2009 he is self-employed. His company is called EvROCS. Prior to that he has worked for several consulting companies in the Netherlands. He has worked on major projects for several clients in industries like entertainment, banking and energy. Erik has been the technical lead of multiple successful high impact data-migration projects moving and transforming large amounts of data. He's been a speaker at several conferences in Europe and the United states.

  • # SQL