ChannelDB2

With expression-based indexes, you can create an index that includes expressions. The performance of queries that  involves expressions is improved if the database manager chooses an index that is created on the same expressions. Expression-based indexes are best suited when you want an efficient evaluation of queries that involve a column expression. Simple index keys consist of a concatenation of one or more specified table columns. Compared to simple indexes, the index key values of expression-based indexes are not the same as values in the table columns. The values are transformed by the expressions that you specify.

You can create the index with the CREATE INDEX statement. If an index is created with the UNIQUE option, the uniqueness is enforced against the values that are stored in the index. The uniqueness is not enforced against the original column values.

  • Create a  table
    • CREATE TABLE EXP_TEST (FIRST CHAR(20), LAST CHAR(20)) ORGANIZE BY ROW
  • Insert  records into the table
    • INSERT INTO EXP_TEST VALUES (‘John’, ‘Stefan’)
  • Create a function based index on the FIRST column
    • CREATE INDEX name_upper on EXP_TEST  (UPPER(FIRST))
    • RUNSTATS ON TABLE EXP_TEST AND INDEXES ALL
  • Examine the explain plans for the following two queries
    • SELECT * FROM EXP_TEST WHERE UPPER(LAST) = ‘Stefan’
    • SELECT * FROM EXP_TEST WHERE UPPER(FIRST) = ‘John’
  • Try some other scalar functions (like functions on date or timestamp columns or string functions on character columns) to see what is possible with function based indexes.
These posts are my opinions and do not necessarily represent IBM’s positions, strategies, or opinions
 

 

Happy Reading!!

Cheers

Ramya Yeleswarapu
mailto :- ramyeles@in[dot]ibm[dot]com

Views: 414

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2019   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service