For decades it's been general good practice not to repeat identical code all over. In SQL the standard way to achieve this has for many years been to write a PL/SQL function and call it from your SQL statements.
The challenge is making these function calls efficient. Oracle Database has many tools to help you with this. These include marking functions deterministic, SQL macros, and the latest enhancement, the SQL transpiler.
With so many options available, how do you know which feature to use and when?
Watch Chris and Kim as they take turns to optimize SQL calling PL/SQL. Learn about the issues with function calls from SQL and the pros and cons of the different ways to address this.
Highlights from this session include:
- 05:10 Demo optimizing PL/SQL in SQL
- 07:40 Scalar subquery optimization
- 12:20 Indexing PL/SQL functions
- 18:50 Non-deterministic functions
- 25:10 Introduction to SQL macros
- 32:30 Demo of SQL macros
- 36:40 Demo of SQL transpiler
- 42:00 Indexes for transpiled functions and macros
- 44:20 Nested function calls
- 52:15 Why use scalar macros
- 1:01:10 Summary of optimizations