Re: Indexing order by desc

On Wed, Mar 19, 2003 at 09:51:01 -0800,
Craig Addleman <CraigA(at)SHARECHIVE(dot)com> wrote:
> 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?

As long as both indexes are used in the same direction, both descending
and ascending ordering will work.

If you have cases where you want a mix, you can create a new operator class
with the reverse ordering and use that for one of columns. I haven't done,
this so I don't know hard it is to do. But it is probably better than munging
the data.