Skip to Main Content

Optimizing functions in the WHERE clause

Calling functions in the WHERE clause can lead to slow SQL. This is because they limit the optimizer's ability to use indexes, can cause bad cardinality estimates, and increase the work the query does. This session covered why functions cause the problems and actions you can take to avoid them. Highlights include: 00:40 - the impact of calling functions in SQL where clauses 04:20 - Why functions prevent the optimizer using indexes 06:40 - Why can't the optimizer rearrange formulas? 12:40 - How to create function-based indexes 13:20 - Demo of indexing date range queries 17:20 - Invisible indexes 25:00 - Summary of problems functions in WHERE cause and function-based indexes 28:10 - Applying the function on INSERT/UPDATE 34:30 - The challenge of ensuring everyone uses the same function 36:15 - Virtual columns 38:10 - Demo of indexing case-insensitive search queries 47:20 - What to do when you have to use functions in a SQL query 49:00 - Optimizing PL/SQL functions in WHERE clauses 50:40 - Using PRAGMA UDF and Scalar SQL macros to improve runtime performance 51:50 - Demo of using PL/SQL functions to do currency conversion 58:00 - Recap of optimizing PL/SQL function in WHERE 58:50 - Final recap Want to learn more about SQL performance tuning? Join this free class on Oracle Dev Gym: https://devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html

Resources

Featured Speakers

  • Speaker

    Chris Saxon


    Oracle Developer Advocate for SQL

Workshop Info

Session Has Completed - 19 April 2022
1 Hour
Write Great SQL Office Hours
English
SQL

Other Upcoming Sessions