Re: IDS Feature Request List (including potential new requests).
bozon wanted row-value comparison, (last_name, first_name, MI, suffix, uid) > (?,?,?,?,?), for next/previous-person paging instead of nested OR conditions the optimizer handled poorly. Serge Rielau asked about the scenario; Obnoxio The Clown suggested a functional index. bozon said it was tried on 9.21 but returned wrong rows versus a table scan; Tech Support called it a known issue. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
bozon wrote: > c) n-tuple comparison in line with the standard. ( it is a standard and > I am tired of working around it with concatentation and padding > variables where needed) Hmm. Interesting. There is a poster in c.d.ibm-db2 who has been asking for this since years, but we were never able to get "critical mass" His point is migration form hierarchical databases (a tight squeeze as a business case). What's your scenario? Cheers Serge -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab
One simple case is the case of previous and next person in sort order
case. We have a standard stateless web application. When we are viewing
a list of persons you can page through each person separately in sort
order which is UPPER(last_name), UPPER(first_name), UPPER(MI), suffix,
UID.
create unique index employee_5ux on (company_id, last_name, first_name,
MI, suffix, UID) ;
In an ideal world we would have a collation that ignored case and the
query would look like this
select first 1
*
from
employee
where
company_id = ? and
( last_name > ? or
( last_name = ? and
( first_name > ? or
( first_name = ? and
( MI > ? or
( MI = ? and
( suffix > ? or
( suffix = ? and uid > ? )
)
)
)
)
)
)
)
order by
company_id, last_name, first_name, MI, suffix, uid;
When we do this the optimizer doesn't seem to use the indexes
consistently probably gets confused with the or's ( I don't know )
Refactoring in clever ways makes it use the index more often. It still
sucks to read compared to
select first 1 * from employee where
company_id = ? and (last_name, first_name, MI, suffix, uid) >
(?,?,?,?,?)
order by
company_id,last_name, first_name, MI, suffix, uid;
I may even have gotten the SQL wrong in the first case which is part of
my point.
I can't believe that this query doesn't come up very often in
applications.
bozon said: > > In an ideal world we would have a collation that ignored case and the Functional index? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com
Thanks, we tried that. In the interest of brevity I left out the whole tragic saga of the functional index. (It is sung to the tune of "The Wreck of the Edmunds Fitzgerald". We are using 9.21.) I initially suggested that we use a functional index until we ran into a problem in testing with it. The functional index wouldn't always return the data that it should. If we forced a scan on the table we would get more rows than when we used the index. Tech Suport said it was a known issue. So in a perfect world we could use a functional index.