Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

April 19, 2022

13:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.


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: .html

Your Experts

    Chris Saxon

    Chris Saxon   

    Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.