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
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.