CODEWITHSUNDEEP
sqlperformanceindexingdb2
Kayser
Kayser

Reputation: 6694

How does DB2 react without the functional based Index

I have a table like

Table1:

col1
col2
col3

and have an index over col3. If I select the data from the table with the following query.

SELECT * FROM Table1 WHERE LCASE(col3) ='abcde'

Does the index help over the performance?

Upvotes: 2

Views: 1744

Answers (2)

Dev
Dev

Reputation: 6776

Now, Db2 finally supports Expression Based Indexes AKA function based index, available in DB2 10.5+

https://www.ibm.com/developerworks/community/blogs/DB2PLSQL/entry/db2_expression_based_indexes?lang=en

Now you can create an index like

CREATE INDEX col3_lcase_idx  on Table1 ( LCASE(col3) )
RUNSTATS ON TABLE Table1 AND INDEXES ALL

Upvotes: 3

beny23
beny23

Reputation: 35018

Short answer: No.

Long answer: No.

Imagine the following contents in your col3:

Foo
bar
baz
foo
BAR

The index on col3 would know this order:

BAR
Foo
bar
baz
foo

However, the order of the query lcase(col3) would be

bar
BAR
baz
foo
Foo

completely different from the index, so the index is of limited use.

However, you could add an automatically created field with the lower case to your table:

ALTER TABLE Table1
      ADD COLUMN col3_lcase VARCHAR(?) 
          GENERATED ALWAYS AS ( LCASE(col3) );
CREATE INDEX col3_lcase_idx ON Table1 ( col3_lcase );

the select above would then use the col3_lcase_idx index.

Upvotes: 2

Related Questions