Problem solve Get help with specific problems with your technologies, process and projects.

Indexing a portion of a column in Oracle

How I can index a fixed part of varchar2 column?

Many SQL "problems" arise from less-than-optimal designs. Needing to index a portion of a column connotes that the portion is somehow significant, and therefore, worthy of its own column. Generally speaking, it's easier to concatenate than to parse. And so, my first recommendation is to redesign, splitting the parts into separate columns.

Another option is to add a column to hold the significant portion, and then index it. This column could be populated by a trigger and/or validated with a CHECK constraint. This option, however, stores redundant information, which, from a design standpoint, makes me squirm a bit.

Another option involves using a functional index, which, as the name implies, allows you to index the results of an expression. Examples could include...

create index PartByCertainPortion on Part (substr(PartID,3,2));
create index EmpByLowerLastName on Employees (lower(LastName));
In order for the optimizer to use the functional index, you must use the same expression in your WHERE clause.
select * from Part 
  where substr(PartID,3,2) = 'PM';
Check your documentation to determine if this feature exists in your version and edition, and what the proper syntax may be.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.