Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle Regular expressions indexes as a tuning tool

Oracle Database Tips by Donald Burleson

Oracle Regular Expression syntax has profound implications for Oracle tuning, especially in the area of indexing where indexes can be created on regular expressions, eliminating expensive full-table scans in-favor of fast index access.  Regular expressions are extremely powerful for extracting facts from large text columns, especially the regexp_like syntax.

Oracle expert Jonathan Gennick notes that regular expression can be used in an Oracle index, a powerful tool for improving the speed of complex SQL queries, and notes that regular expression indexes (a type of function-based index) can dramatically reduce database overhead for pattern-matching queries.  I've noted in my book "Oracle Tuning: The Definitive Reference", that function-based indexes are one of the most powerful and underutilized tools for Oracle professionals.

Here are some little know facts about regular expressions:

  • They can be used with bind variables

  • They can be included in function-based indexes

Indexing on regular expressions

In Parsing with regular expressions regexp_like Jonathan Gennick shows a great example where we use Oracle regular expressions to extract �acreage� references from inside a text string, ignoring important factors such as case sensitivity and words stems (acre, acres, acreage):

COLUMN park_name format a30
COLUMN acres     format a13
  REGEXP_SUBSTR(description,'[^ ]+[- ]acres?',1,1,'i') acres
  FROM michigan_park
  WHERE REGEXP_LIKE(description, '[^ ]+[- ]acres?','i');

Here is the output, where we see that the regular expression has parsed-out the acreage figures, just as-if they were a discrete data column with the table:

PARK_NAME                       ACRES
____________________________    ___________
Mackinac Island State Park      1800 acres
Muskallonge Lake State Park     217-acre
Porcupine Mountains State Park  60,000 acres
Tahquamenon Falls State Park    40,000+ acres

The only problem with this query is that it will always perform a large-table full-table scan on the michigan_park table, causing unnecessary overhead for Oracle.

However, using the powerful function-based indexes we could eliminate the unnecessary overhead by using the regular expression directly in the index.



 (REGEXP_LIKE(description, '[^ ]+[- ]acres?','i'));

However, Laurent Schneider notes that it is illegal to have an index on a Boolean function, but you could have an index on a case expression returning 1.

create index
  (case when
      description like '_% acre%'
      description like '_%-acre%'
   then 1 end);

   (case when
      description like '_% acre%'
      description like '_%-acre%' then 1 end)
is not null;

This simple index definition would create a yes/no index on all park records that contain a reference to "acre", "acres", "acreage".  The database overhead would be once, when each rows is added to the table, and not over-and-over again when queries are executed.

The rules for choosing a function-based index on a complex expression (regular expression, decode) is a trade-off between several factors:

  • The number of blocks in the table - A full-table scan of a super-large table can cause I/O contention.

  • The percentage of rows returned - If the regular expression returns only a small percentage of the total table rows, a regular expression index will greatly reduce I/O.

  • The frequency of the query - If the query is executed frequently, Oracle may do millions of unnecessary full-table scans.

  • The tolerance for slower row inserts - Parsing the text column at insert time (to add the row to the regular expression index) will slow-down inserts.

It's the age-old quandary. If we build the regular expression once (at insert time) it can be used over-and-over again with little overhead.  Conversely, using regular expressions in SQL without a supporting index will cause repeated full-table scans.

For detailed information on learning regular expression and using them in the database see Jonathan Gennick and Peter Linsley's book Oracle Regular Expressions Pocket Reference.
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.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.