Regular Expressions and Oracle Performance

Oracle Database Tips by Donald Burleson

But regular expression are notorious for causing full-table scans on multi-million rows tables, and special care must be given to creating matching function-based indexes to keep performance at an acceptable level.

From a performance perspective it's important to properly index the Oracle tales so that regular expression validation does not cause problematic full-table scans. Oracle syntax allows the use of function-based indexes (FBI's) to minimize the amount of database I/O associated with a invocation of a regular expression.

See Oracle Performance and Regular Expressions

Function-based indexes cannot work for all regular expressions, however, and it can be tricky to remove unnecessary full-table scans on some regular expressions, especially regexp_like. See these related notes on tuning with function-based indexes:

Oracle function based index - SQL WHERE clause predicate invalid

Regular Expressions and Oracle Performance function based indexes

Get the Complete
Oracle SQL Tuning Information

The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

��