08.10.2009, в 0:30, Ann W. Harrison написал(а):

> 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
>

Ann,

let me ask some questions and do some remarks.

a) In theory: sort in memory costs N*log(N) + reading whole table O(N);
sort with index costs reading whole table with index O(N). We can't
state without additional conditions that first is cheaper, can we?

b) Additional conditions must allow us to compare O(N) in two cases.
Taking into account:
- sizes of numerous caches throw which data pass while reading from
disk to memory
- modern intelligent disk controllers that may reorder physical reading
- RAID etc.
we can't use "disk order" conseption like it was years ago.
Imaging that whole database fit into cache in main memory use of index
is faster.

c) On the other side, what will occur if data to be sorted don't fit
into memory?
Page file on disk is used, isn't it?. And this may happen easily when
many
user's questions are proccessed simultaneously.

d) When server use index it can begin to return data to client
immediately. And (may be) freeing memory immediately.

Regards

Valeri