Index on UPPER(col1)

Database
Enthusiast

Index on UPPER(col1)

I discovered last night from UseTheIndexLuke that if there's an index on col1 and an often-used query uses UPPER(col1), the index will not be used because UPPER(col1) is not the same as col1. So the author recommended to use UPPER(col1) if it's an used often on the table.

Does this hold true for Teradata? Would I be able to gain much if I created an index on UPPER(col1) on top of col1?

Thanks

Tags (1)
2 REPLIES
Senior Apprentice

Re: Index on UPPER(col1)

#1: There are no expression-based indexes in Teradata

#2: When you need a case-insensitive comparison in Teradata you simply define the column as NOT CASESPECIFIC (thus avoiding the UPPER). This is the default in a Teradata session, in an ANSI session you need to modify you comparison to indexedcol = 'value' (not casespecific)

#3: UseTheIndexLuke is mainly aimed at OLTP databases using BTree indexes, but Teradata is a DWH database and Teradatas index are mostly hash-based :-)

Enthusiast

Re: Index on UPPER(col1)

Ah thanks. I'll look more into the casespecific definitions on my tables