Subject Re: index desc
Author Adam
--- In, bill_lam <bill_lam@...> wrote:
> Suppose I am going to select in both ascending and desending order.
> select * from foo order by a
> select * from foo order by a desc
> Is only one index for ascending order (default) sufficient, or I
have to use 2
> index, one ascend and the other desc?
> create index foo_a on foo(a)
> create desc index foo_a_d on foo(a)

I would suggest you use no index for ordering, unless there is a min,
a max, or a first n in your select.

If you do want to use an indexed sort (again only advised in the case
of min, max, or first n), then you will need to declare both.

The overheads of an indexed read tend to be higher than the overhead
of an in memory sort.

> Also is it possible to create a composite index on some ascending
column and
> some descending column, like equivalent of
> create index foo_inx on foo(a , b desc, c)

That would take you a whole 10 seconds to find out via experiment,
less time than it took for you to type this paragraph.