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)