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