All Forums Database
brim 10 posts Joined 09/14
22 Oct 2014
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?

dnoeth 4628 posts Joined 11/04
22 Oct 2014

#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 :-)


brim 10 posts Joined 09/14
22 Oct 2014

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

You must sign in to leave a comment.