@Dani Schnider works as a database consultant for more than 20 years.
He finished his studies in computer science at ETH Zurich in 1990.
Since 1994, he is developing data models and applications with Oracle databases (version 6 to 21c), since 1997 mainly in data warehousing projects.
Most of his time he is doing jobs for customers in design, data modeling, ETL development, architecture reviews and performance optimization of Oracle data warehouses.
He is teacher for several Oracle trainings and co-author of the (German) books Data Warehousing mit Oracle – Business Intelligence in der Praxis and Data Warehouse Blueprints.
Dani is Oracle ACE Director and writes about Data Warehousing with Oracle on his blog, publishes articles and white papers and gives presentations on conferences.
During Contech2022,Dani Schnider will present Function Calls in SQL – Black Box for the Optimizer?
| Function Calls in SQL – Black Box for the Optimizer? – Abstract: Function calls in SQL statements are a common reason for performance issues in many database applications. Many developers are not aware of their impact to the query optimizer. The purpose of this presentation is to show how Oracle handles SQL and PL/SQL functions in SQL queries. |
| Description: Functions in SQL are a useful and popular feature that is often used. However, little attention is often paid to performance aspects. A function call or an expression in a WHERE condition is a challenge for the query optimizer that often leads to unsuitable execution plans and bad response times. For PL/SQL functions called from SQL, the situation is even more complex. If used incorrectly, they can lead to massive performance degradation. In this presentation, I will show different possibilities to support the optimizer in function calls to generate better execution plans. Furthermore, I will explain how PL/SQL functions can be used optimally in SQL queries and which performance killers must be avoided. Of course, all this will be demonstrated with live examples and stories from the field. As each variant has advantages and disadvantages. At the end of the lecture, you will take home different modelling patterns and know when which one fits and what the strengths of these patterns are. Just as a wizard or witch knows the effect of magic spells. |