I don't want to comment negatively on functional indexes or any other
kind of indexes. They can help, but they best stay out of the table
column definition. Even a simple index is just a way to help in search
speed, or validate a uniqueness, helping to alleviate the fact data
processors (computers) do not have infinite speed.

Concerning row-value constructor support, and hope, OK, the discussion
just started (...mid 1998 with the upcoming DB2 V5.2).

Bernard (Dhooghe)


On May 8, 5:37 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> Bernard Dhooghe wrote:
> > On May 7, 3:54 pm, Tonkuma <tonk...@fiberbit.net> wrote:
> >> I couldn't understand your issue(might be by my poor English
> >> capability).
>
> >> Here are some thoughts which are inspired by your article.
> >> 1) Although it is not documented, you can specify row comparison
> >> predicate in(on?) DB2 for LUW 9.1.
> >> For example:
> >> ------------------------------ Commands Entered
> >> ------------------------------
> >> SELECT empno, workdept
> >> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> >> , edlevel
> >> FROM employee
> >> WHERE (workdept, edlevel) = ('D11', 16)
> >> ;
> >> ------------------------------------------------------------------------------
>
> >> EMPNO WORKDEPT FULLNAME EDLEVEL
> >> ------ -------- ------------------------------ -------
> >> 000060 D11 IRVING F STERN 16
> >> 000150 D11 BRUCE ADAMSON 16
> >> 000170 D11 MASATOSHI J YOSHIMURA 16
> >> 000190 D11 JAMES H WALKER 16
> >> 000200 D11 DAVID BROWN 16
> >> 200170 D11 KIYOSHI YAMAMOTO 16
>
> >> 6 record(s) selected.
>
> >> And you can specify full-select (including VALUES clause) in predicate
> >> on DB2 for LUW prior V9.1.
> >> Here are two examples:
> >> ------------------------------ Commands Entered
> >> ------------------------------
> >> SELECT empno, workdept
> >> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> >> , edlevel
> >> FROM employee
> >> WHERE (workdept, edlevel) = ANY (VALUES ('D11', 16) )
> >> ;
> >> ------------------------------------------------------------------------------
>
> >> EMPNO WORKDEPT FULLNAME EDLEVEL
> >> ------ -------- ------------------------------ -------
> >> 000060 D11 IRVING F STERN 16
> >> 000150 D11 BRUCE ADAMSON 16
> >> 000170 D11 MASATOSHI J YOSHIMURA 16
> >> 000190 D11 JAMES H WALKER 16
> >> 000200 D11 DAVID BROWN 16
> >> 200170 D11 KIYOSHI YAMAMOTO 16
>
> >> 6 record(s) selected.
>
> >> ------------------------------ Commands Entered
> >> ------------------------------
> >> SELECT empno, workdept
> >> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> >> , edlevel
> >> FROM employee
> >> WHERE (workdept, edlevel) IN (VALUES ('D11', 16), ('D11', 17) )
> >> ;
> >> ------------------------------------------------------------------------------
>
> >> EMPNO WORKDEPT FULLNAME EDLEVEL
> >> ------ -------- ------------------------------ -------
> >> 000060 D11 IRVING F STERN 16
> >> 000150 D11 BRUCE ADAMSON 16
> >> 000160 D11 ELIZABETH R PIANKA 17
> >> 000170 D11 MASATOSHI J YOSHIMURA 16
> >> 000180 D11 MARILYN S SCOUTTEN 17
> >> 000190 D11 JAMES H WALKER 16
> >> 000200 D11 DAVID BROWN 16
> >> 000210 D11 WILLIAM T JONES 17
> >> 200170 D11 KIYOSHI YAMAMOTO 16
>
> >> 9 record(s) selected.
>
> >> 2) One usage of generated column is to create a functional index.http://groups.google.com/group/comp....rowse_frm/thre...
>
> > For point 2: I know of this, unfortunately, it breaks the relational
> > model, a base table contains a column that is not a column; an index
> > (functional or not) should not impact a table structure, generated
> > columns do, in french they call it "une fausse bonne idée" (an idea
> > that looks good but isn't)
>
> Let's not get all excited about this relational part.
> The REASON for the avoidance of functionally dependent columns is the
> risk of inconsistency. expression-generated columns assert consistency.
> Thus there is no problem. So let's not get hung up by the letter of the
> law and stick with it's spirit.
> This good idea has since been copied by both MS SQL Server and Oracle
> (which has expression based indexes, so they must have seen some
> goodness in it beyond mere indexing).
> I can't help but being proud :-)
>
> > Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
> > clause ?
>
> Believe it or not. Actually making some progress towards your pet peeve.
> Don't give up hope.
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab