Author: Burleson

Fast Index Access:

Fast index access requires the use of expensive full table scans. Oracle Regular Expressions plays an important role especially in the area of oracle indexing. Regular Expressions act as a powerful tool for information extraction and hence can be beneficial in retrieving data from large text columns.

Using regular Expressions with Indexes:

Apart from using regular expressions with bind variables we can use them with function based indexes. Such indexes can boost the performance of an oracle application. They can improve the speed of complex SQL Queries and also reduce the overhead of complex pattern matching queries in the databases. Such function based indexes are one of the most powerful tools for oracle professionals.

Function-based index on a Regular Expression –Yes or No?

The decision to use a function-based index on a regular expression is based on several factors.

I/O Contention:

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

I/O Reduction:

A regular expression index will greatly reduce I/O if it returns only a small percentage of the total table rows.

Unnecessary FTS:

Oracle may do unnecessary full table scans if the query is executed frequently.

Slower Inserts:

If text column need to be parsed at insert time in order to add row to a regular expression index then it will result in slower inserts.

Oracle Regular Expressions:

Oracle Regular Expressions can be used to extract text matching a particular pattern. Using regular expressions ignores factors like case sensitivity and word stems.

An Example:

Consider the below example Consider the below example by Jonathan Gennick which extracts “acreage” references from inside a test string.

COLUMN park_name format a30
COLUMN acres format a13

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

The regular expression parses out the acreage figures as-if they are a discrete data column with the table. This query causes unnecessary overhead for oracle by performing a large-table full-table scan on the table.

Using Function based Indexes:

The powerful function-based indexes use the regular expression directly in the index, and hence reduce the overhead.

CREATE INDEX   parks_acreage ON michigan_parks (REGEXP_LIKE(description, '[^ ]+[- ]acres?','i'));

Function based Indexes returning value:

An index can be made on a case expression returning 1. Below index definition will create a yes/no index on all records that contain a reference to "acre", "acres", "acreage". When rows are added to the table and queries are executed then the database overhead will be once.

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

select * from michigan_parks where
(case when description like '_% acre%' or description like '_%-acre%' then 1 end) is not null;

Conclusion:

To conclude I would say that that if regular expressions are built once at insert time only then it can give performance boost by reducing overhead but if we use it without indexes then it will cause unnecessary FTS and hence add overhead to the system.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Identify the root causes of Poor Oracle Performance!!
   Security Alert - Database Worms
   Oracle Recovery from an Unplanned Outage!!
   Tricky Oracle Recovery for missing Archive Log!!