Our application has a pair of list boxes which enable the user to control
the order of rows returned. By selecting two columns out of a total of 5
possibilities, the result set is ordered by those 2 cols. For example, a
user can order by 'username' and 'udate' or 'username' and 'status'. Without
indexing on pairs of columns, execution time takes far too long. The problem
is with 'order by descending' since we can't create descending indexes.
We have a potential solution: convert dates to negative integers (2003-03-19
becomes -20030319), and convert characters to their alphabet-position
opposites: AbCz becomes zYxA. These converted values will require
additional columns in the table (a materialized view). 100 indexes are
needed in order to index all combinations of the five columns. It's a
kludge, but it looks like it would work. Does anyone have a better solution?
Craig Addleman
DBA
ShareChive LLC
craiga@sharechive.com