Hi Kokok,



The only way to achieve this is to have an inverted/negative calculated
column and create the index on that.



Example:

Create table test(A integer, B integer, C integer)

Create index IDXTest on test (c, b).



In a trigger Before Insert or Update:

New.c = -new.A;





Not very pretty but this will give you what you want.



Cheers,



Calin Pirtea



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of kokok_kokok
Sent: Thursday, 8 October 2009 9:30 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: How to mix ascending and descending
fields in one index





The query is a basic example, in reality the consult is paged. It sends
the partial results from the middle tier to the client tier. The client
tier gets the next "page" of records in each user iteration.

I need the results to be indexed, in other case, if the sort is after
the read the entire table, the costs is not viable since it can have
millions of records. For example, using the index (a,b), the result is
in 0.01 seconds, if the index is not used, the result can take 20
seconds or more. In few words, index is absolutely necessary.

It works really fine using the index (a,b), but I would need (a desc,
b). This composed index is possible in other SQL platforms, but I cannot
see the way in FB.

--- In firebird-support@yahoogroups.com
<mailto:firebird-support%40yahoogroups.com> , "Ann W. Harrison"
<aharrison@...> wrote:

>
> kokok_kokok wrote:
> > If I
> > If the index is (a,b) then
> >
> > "select * from foo order by a, b" uses the index in the plan, but

what I need is:

> >
> > "select * from foo order by a desc, b"
> >
> >
> > How can I create a index to be used in the above sql statement?
> >
>
> You can't. Nor should you want to. Everybody with even a vague
> recollection of a computer science course knows that the cost of
> a sort is nlog(n) while reading from an index is linear with the
> number of records to be read. However the cost of reading records
> from the disk in index order - random relative to disk placement -
> overwhelms the cost of the sort. If you're reading a whole table,
> its much faster to read it in disk order and sort the result rather
> that rattle the disk around finding a record here, a record there.
>
> The exception is a query with a limit/first clause - but even there
> an index on A and a sort on B shouldn't be horribly expensive. If
> it is, then store a pseudo field that's like B, but inverted.
> That's what our descending indexes are.
>
> Good luck,
>
> Ann
>

[Non-text portions of this message have been removed]