Erik van Roon
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 standalone or packaged functions and procedures 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 difference in performance you can expect, compared to each other and to using conventional function calls. Furthermore it will highlight the pitfalls that can be encountered.
Erik van Roon is an 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. In 2015 Erik was nominated and selected to be a finalist in the SQL category of OTN Developers Choice Awards.