Functional Index.. How to?

[WantedToBeDBA]

Hi Friends,
How to create the functional index in db2? Is it possible to do
that? I refered in online doc. But i am unable to find anything..

Thanks,
Thiru.
WantedToBeDBA.

[Serge Rielau]

DB2 does not support fucntional indexes. Teh closest match woudl be a
column genefrated by an expression. It is the same as a functional index
in the sense that you can exploit indexes on predicates with expressions.
The upside is that you can also benefit from the feature on table
access. The downside is that it takes memory in the data page.

Example:
CREATE TABLE T(c1 varchar(10), uc1 GENERATED ALWAYS AS (UPPER(c1)));
CREATE INDEX i1 ON T(uc1);
INSERT INTO T(c1) VALUES ('hello'), ('world');
SELECT c1 FROM T WHERE UPPER(c1) = 'HELLO';

Cheers
Serge

[ak_tire...@yahoo.com]

Serge,
are you saying that the optimizer could choose INDEX i1 for the query
SELECT c1 FROM T WHERE UPPER(c1) = 'HELLO'; ?
Is the query rewritten as SELECT c1 FROM T WHERE uc1 = 'HELLO'? I don't
have the latest the greatest release

[Serge Rielau]

Correct. The feature was added in DB2 V7.1 so unless you are out of date
beyond hope you will have it. It's quite popular. Lots of posters in
this group us it.

Cheers
Serge

[Thiru]

Serge,
What will if the case be like this..

select max(numberic column) from tablename where <conditions>
Thiru
WantedToBeDBA.

[Serge Rielau]

You mean you want to precompute the max()? Neither functional indexes
nor generated columns help here since both operate on a per/row basis.
What you need here are Materializeed Query Tables (MQT) (aka
"materialized views" or "indexed views" if you talk Oracle or MS SQL
Server speak).
Take a look at the CREATE TABLE statement with the AS clause.

Cheers
Serge

[ak_tire...@yahoo.com]

>> What you need here are Materializeed Query Tables (MQT)

...<<
depending on the conditions, there could be a lot of lock contention on
the MQT. Just imagine an MQT defined as select max(numberic column)
from tablename <no where clause>. If you set the MQT up as refresh
immediate, you have in fact serialized access to the table, as only one
transaction at a time has an update lock on the only row in the MQT.
If you don't want stale data (refresh deferred) and cannot afford that
much lock contention, you might find index covering a better
alternative.
Also you could add a column to MQT's definition to reduce lock
contention, like this:
select max(numberic column), another_column from tablename group by
another_column

[Serge Rielau]

The lock on the MQT would be per group. Presuming decent access
serialization would thus also be per group.
There is no free lunch.....

Cheers
Serge

[ak_tire...@yahoo.com]

Serge,

I'm not sure I'm following you. modifying 1 row in the base tqble will
lock only one row in the MQT.
When you say 'per group', do you mean the group in MQT definition?

BTW, using incremental refresh, we can join a refresh-deferred MQT with
its staging table. Since all the modifications insert to the staging
table, there is no additional lock contention. As long as both the MQT
and the staging table are small, the join is a snap - you get the
current information real quick.Naturally one need to refresh
frequently, otherwise the join slows down.

Although this approach works, it's a little bit tricky, so I'd prefer
index covering most of the time

[Serge Rielau]

Maybe there was a misunderstanding.
I understood the post meant that adding an MQT would serialize updates
due to locking.
Let's assume we an MQT which rolls events into a months.
It si correct that if I update two separate events in the same month
there will be a lock collision on the row of the MQT olding teh rolled
up information. Two events updated in separate months howvere would not
collide.

Cheers
Serge

[ak_tire...@yahoo.com]

>> Let's assume we an MQT which rolls events into a months.
It si correct that if I update two separate events in the same month
there will be a lock collision on the row of the MQT olding teh rolled
up information. Two events updated in separate months howvere would not

collide
.. <<

Yes, of course, I would completely agree. However, sometimes it is not
easy to get rid of collisions - whatever GROUP BY we try to use, some
statement will eventually touch a row in every group, effectively
locking the whole table.

BTW, in certain cases we do want to serialize and lock contention on
MQT is OK