Table definition:

CREATE TABLE "SCHEMA1 "."X2" (
"C1" CHAR(20) NOT NULL ,
"C2" CHAR(10) NOT NULL ,
"C3" CHAR(30) NOT NULL GENERATED ALWAYS AS (C1||
C2) )
IN "USERSPACE1" ;

-- DDL Statements for primary key on Table "SCHEMA1 "."X2"

ALTER TABLE "SCHEMA1 "."X2"
ADD CONSTRAINT "P1" PRIMARY KEY
("C1",
"C2");

IBM Data Studio Version 1.1.1.

Try edit data , insert row. Will fail.

The editor has problems with the generated columns.

I would say: great.

The generated columns capability breaks the relational model. What is
a column in a base table that is not a column?

It has been discussed already several times in the past, but one usage
of generated columns in DB2 LUW is due to the the lack of (SQL92!)
support of row value constructor in the where clause (and to close the
circle, in the cursor positioning clause as extension to SQL92 but
compatible with it in it's simple format) and corresponding optimizer
support.

Quo usque ...

Bernard Dhooghe

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....3a c5739087f7

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)

Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
clause ?

Bernard Dhooghe

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

> Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
> clause ?
Is it equvalent to following example?
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel)
= ANY (VALUES (workdept, edlevel), ('D11', 17)
ORDER BY 1 DESC, 2 DESC
FETCH FIRST 1 ROWS ONLY)
ORDER BY
workdept, edlevel
;
------------------------------------------------------------------------------

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000160 D11 ELIZABETH R PIANKA 17
000180 D11 MARILYN S SCOUTTEN 17
000210 D11 WILLIAM T JONES 17
000220 D11 JENNIFER K LUTZ 18
200220 D11 REBA K JOHN 18
000230 D21 JAMES J JEFFERSON 14
000250 D21 DANIEL S SMITH 15
000270 D21 MARIA L PEREZ 15
000070 D21 EVA D PULASKI 16
000260 D21 SYBIL P JOHNSON 16
000240 D21 SALVATORE M MARINO 17
200240 D21 ROBERT M MONTEVERDE 17
000050 E01 JOHN B GEYER 16
000290 E11 JOHN R PARKER 12
000310 E11 MAUDE F SETRIGHT 12
200310 E11 MICHELLE F SPRINGER 12
000300 E11 PHILIP X SMITH 14
000090 E11 EILEEN W HENDERSON 16
000280 E11 ETHEL R SCHNEIDER 17
200280 E11 EILEEN R SCHWARTZ 17
000100 E21 THEODORE Q SPENSER 14
000330 E21 WING LEE 14
200330 E21 HELENA WONG 14
000320 E21 RAMLAL V MEHTA 16
000340 E21 JASON R GOUNOT 16
200340 E21 ROY R ALONZO 16

26 record(s) selected.


( c1,c2,..) <= (value1,value2, ...)
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel)
= ANY (VALUES (workdept, edlevel), ('D11', 17)
ORDER BY 1, 2
FETCH FIRST 1 ROWS ONLY)
ORDER BY
workdept, edlevel
;
------------------------------------------------------------------------------

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000120 A00 SEAN O'CONNELL 14
200120 A00 GREG ORLANDO 14
000010 A00 CHRISTINE I HAAS 18
200010 A00 DIAN J HEMMINGER 18
000110 A00 VINCENZO G LUCCHESSI 19
000020 B01 MICHAEL L THOMPSON 18
000130 C01 DELORES M QUINTANA 16
000140 C01 HEATHER A NICHOLLS 18
200140 C01 KIM N NATZ 18
000030 C01 SALLY A KWAN 20
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
000160 D11 ELIZABETH R PIANKA 17
000180 D11 MARILYN S SCOUTTEN 17
000210 D11 WILLIAM T JONES 17

19 record(s) selected.

> Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
> clause ?
Is it equivalent to follwing examples?
( c1,c2,..) >= (value1,value2, ...)
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE ('D11', 17)
= ANY (VALUES (workdept, edlevel), ('D11', 17)
ORDER BY 1, 2
FETCH FIRST 1 ROWS ONLY)
ORDER BY
workdept, edlevel
;
------------------------------------------------------------------------------

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000160 D11 ELIZABETH R PIANKA 17
000180 D11 MARILYN S SCOUTTEN 17
000210 D11 WILLIAM T JONES 17
000220 D11 JENNIFER K LUTZ 18
200220 D11 REBA K JOHN 18
000230 D21 JAMES J JEFFERSON 14
000250 D21 DANIEL S SMITH 15
000270 D21 MARIA L PEREZ 15
000070 D21 EVA D PULASKI 16
000260 D21 SYBIL P JOHNSON 16
000240 D21 SALVATORE M MARINO 17
200240 D21 ROBERT M MONTEVERDE 17
000050 E01 JOHN B GEYER 16
000290 E11 JOHN R PARKER 12
000310 E11 MAUDE F SETRIGHT 12
200310 E11 MICHELLE F SPRINGER 12
000300 E11 PHILIP X SMITH 14
000090 E11 EILEEN W HENDERSON 16
000280 E11 ETHEL R SCHNEIDER 17
200280 E11 EILEEN R SCHWARTZ 17
000100 E21 THEODORE Q SPENSER 14
000330 E21 WING LEE 14
200330 E21 HELENA WONG 14
000320 E21 RAMLAL V MEHTA 16
000340 E21 JASON R GOUNOT 16
200340 E21 ROY R ALONZO 16

26 record(s) selected.

or
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel)
= ANY (VALUES (workdept, edlevel), ('D11', 17)
ORDER BY 1 DESC, 2 DESC
FETCH FIRST 1 ROWS ONLY)
ORDER BY
workdept, edlevel
;


And for ( c1,c2,..) <= (value1,value2, ...)
------------------------------ Commands Entered
------------------------------
SELECT empno, workdept
, firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
, edlevel
FROM employee
WHERE (workdept, edlevel)
= ANY (VALUES (workdept, edlevel), ('D11', 17)
ORDER BY 1, 2
FETCH FIRST 1 ROWS ONLY)
ORDER BY
workdept, edlevel
;
------------------------------------------------------------------------------

EMPNO WORKDEPT FULLNAME EDLEVEL
------ -------- ------------------------------ -------
000120 A00 SEAN O'CONNELL 14
200120 A00 GREG ORLANDO 14
000010 A00 CHRISTINE I HAAS 18
200010 A00 DIAN J HEMMINGER 18
000110 A00 VINCENZO G LUCCHESSI 19
000020 B01 MICHAEL L THOMPSON 18
000130 C01 DELORES M QUINTANA 16
000140 C01 HEATHER A NICHOLLS 18
200140 C01 KIM N NATZ 18
000030 C01 SALLY A KWAN 20
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
000160 D11 ELIZABETH R PIANKA 17
000180 D11 MARILYN S SCOUTTEN 17
000210 D11 WILLIAM T JONES 17

19 record(s) selected.

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

On May 9, 2:54 am, Tonkuma <tonk...@fiberbit.net> wrote:
> > Point 1: what about ( c1,c2,..) >= (value1,value2, ...) in where
> > clause ?
>
> Is it equivalent to follwing examples?
> ( c1,c2,..) >= (value1,value2, ...)
> ------------------------------ Commands Entered
> ------------------------------
> SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE ('D11', 17)
> = ANY (VALUES (workdept, edlevel), ('D11', 17)
> ORDER BY 1, 2
> FETCH FIRST 1 ROWS ONLY)
> ORDER BY
> workdept, edlevel
> ;
> ------------------------------------------------------------------------------
>
> EMPNO WORKDEPT FULLNAME EDLEVEL
> ------ -------- ------------------------------ -------
> 000160 D11 ELIZABETH R PIANKA 17
> 000180 D11 MARILYN S SCOUTTEN 17
> 000210 D11 WILLIAM T JONES 17
> 000220 D11 JENNIFER K LUTZ 18
> 200220 D11 REBA K JOHN 18
> 000230 D21 JAMES J JEFFERSON 14
> 000250 D21 DANIEL S SMITH 15
> 000270 D21 MARIA L PEREZ 15
> 000070 D21 EVA D PULASKI 16
> 000260 D21 SYBIL P JOHNSON 16
> 000240 D21 SALVATORE M MARINO 17
> 200240 D21 ROBERT M MONTEVERDE 17
> 000050 E01 JOHN B GEYER 16
> 000290 E11 JOHN R PARKER 12
> 000310 E11 MAUDE F SETRIGHT 12
> 200310 E11 MICHELLE F SPRINGER 12
> 000300 E11 PHILIP X SMITH 14
> 000090 E11 EILEEN W HENDERSON 16
> 000280 E11 ETHEL R SCHNEIDER 17
> 200280 E11 EILEEN R SCHWARTZ 17
> 000100 E21 THEODORE Q SPENSER 14
> 000330 E21 WING LEE 14
> 200330 E21 HELENA WONG 14
> 000320 E21 RAMLAL V MEHTA 16
> 000340 E21 JASON R GOUNOT 16
> 200340 E21 ROY R ALONZO 16
>
> 26 record(s) selected.
>
> or
> ------------------------------ Commands Entered
> ------------------------------
> SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE (workdept, edlevel)
> = ANY (VALUES (workdept, edlevel), ('D11', 17)
> ORDER BY 1 DESC, 2 DESC
> FETCH FIRST 1 ROWS ONLY)
> ORDER BY
> workdept, edlevel
> ;
>
> And for ( c1,c2,..) <= (value1,value2, ...)
> ------------------------------ Commands Entered
> ------------------------------
> SELECT empno, workdept
> , firstnme || RTRIM(' '||midinit) || ' '||lastname AS fullname
> , edlevel
> FROM employee
> WHERE (workdept, edlevel)
> = ANY (VALUES (workdept, edlevel), ('D11', 17)
> ORDER BY 1, 2
> FETCH FIRST 1 ROWS ONLY)
> ORDER BY
> workdept, edlevel
> ;
> ------------------------------------------------------------------------------
>
> EMPNO WORKDEPT FULLNAME EDLEVEL
> ------ -------- ------------------------------ -------
> 000120 A00 SEAN O'CONNELL 14
> 200120 A00 GREG ORLANDO 14
> 000010 A00 CHRISTINE I HAAS 18
> 200010 A00 DIAN J HEMMINGER 18
> 000110 A00 VINCENZO G LUCCHESSI 19
> 000020 B01 MICHAEL L THOMPSON 18
> 000130 C01 DELORES M QUINTANA 16
> 000140 C01 HEATHER A NICHOLLS 18
> 200140 C01 KIM N NATZ 18
> 000030 C01 SALLY A KWAN 20
> 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
> 000160 D11 ELIZABETH R PIANKA 17
> 000180 D11 MARILYN S SCOUTTEN 17
> 000210 D11 WILLIAM T JONES 17
>
> 19 record(s) selected.

This query is indeed a creative manner to select the rows!

But the optimizer will see what is going on.

Writing the where clause as

workdept ='D11' and edlevel >=16 or workdept>'D11' optimize for 1
rows will also work, the optimizer will do the following:


select *
from employee
where workdept='D11'and edlevel >=16 or workdept > 'D11'
order by workdept, edlevel
optimize
for 1 rows


Section Code Page = 819

Estimated Cost = 90.020790
Estimated Cardinality = 13.550994

Access Table Name = BDH002.EMPLOYEE ID = 2,5
| Index Scan: Name = BDH002.X1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: WORKDEPT (Ascending)
| | | 2: EDLEVEL (Ascending)
| #Columns = 14
| Volatile Cardinality
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | #Predicates = 3
Return Data to Application
| #Columns = 14

End of section


So the optimizer does not see what pivot value D11 is, and starts at
beginning of index, not at the D11 value.

Taking one one value, it will the start value:

select *
from employee
where workdept>='D11'
order by workdept, edlevel
optimize
for 1 rows


Section Code Page = 819

Estimated Cost = 55.268456
Estimated Cardinality = 15.987519

Access Table Name = BDH002.EMPLOYEE ID = 2,5
| Index Scan: Name = BDH002.X1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: WORKDEPT (Ascending)
| | | 2: EDLEVEL (Ascending)
| #Columns = 14
| Volatile Cardinality
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: 'D11'
| | Stop Key: Exclusive Value
| | | | 1: NULL
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Return Data to Application
| #Columns = 14

End of section


With row-value constructor in where, the optimizer is closer to the
semantics and will be able to fully use an index if one present.


Bernard (Dhooghe)