Function-based indexes in DB2

[leo6]

Hi ,

I have a DB2 SQL query like :

select a.col1,b.col1
from tab1 a, tab2 b
where a.col1=b.col1
and lower(a.col2) = <value>

how can i create an index on the function, lower(a.col2) so that the query uses the index.
currently index for a is on (col1,col2) but the query for a goes for a TBSCAN .
is there an alternative to this ??
could anyone help us on this..

Thank you.

 

[rbod]

Are the stats uptodate? [reorg/runstats]

I have tried your query and it is doing all index scans for me.

Also, why are you selecting both a.col1 and b.col1, they have same values[a.col1=b.col1] ?

 

[leo6]

Hi ,

Thanks for the response.Actually this query has got additional columns of tab2 in the WHERE clause.Basically. I would like to know how to create indexes over functions applied on columns of the table.
In this case, can i create one, on the function,
"LOWER(a.col2) " ?

And I am just selecting them to display the field values
of both table a and b.Yes,they would be same,but for the sake of just displaying I have put it in the query.

select a.col1,a.col2,b.col3,b.col4
from tab1 a, tab2 b
where a.col1=b.col1
and lower(a.col2) = <value>
and b.col5 is null ;


b.col5 is a foreign key on the same table,b for pk- b.col1

But I am interested in knowing how to create an index on LOWER(a.col2), instead of a normal index that is created on a.col2.

Would like to have your inputs on this.

 

[rbod]

An index is an ordered set of pointers to rows in a base table. Each index is based on the values of data in one or more table columns.
LOWER is a scalar function. It returns the string in lower case characters.

You cannot create an index on the function.

 

[leo6]

Hi ,

But, Oracle has got a concept of function-based indexes where the index will be used when you use that particular function over the column specified in the WHERE clause of the query.So they create index on "func(col1) " instead of col1.
Does DB2 has something of this sort ?

Thank you.

 

[juliane26]

Well today you could go along with an calculated column or an index extension.

Since these index extensions are somehow not trivial, I used calculated columns for the functions I needed most.

If I remember correctly from a discussion IBM plans to ship function indexes at least for string functions in V9.




Juliane