Subject | Re: [IBDI] Re: Firebird 1 |
---|---|

Author | Ann W. Harrison |

Post date | 2001-06-06T17:31:58Z |

David Jenks wrote:

the result set is not sorted. The other is when the result set

is sorted in such a way that the engine can read the rows in

index order. At one time, the optimizer (which I've avoided of

late) recognized the special case that FIRST <n> .... SORTED BY

should walk an index (if one exists) rather than retrieving the

whole set and sorting it. However when you get into joins and

result sets that are ordered by terms in different tables, the

optimizer reverts to the find 'em and sort 'em strategy.

Obviously, the cost of the latter (to the engine) is s * i,

where 's' is the size of the total result set and 'i' is the

number of iterations of the query. It's only the cases where

the engine can avoid building the whole result set every time

that my other formula applies ... and my sample computation

was off by a power of ten ... sigh.

Regards,

Ann

www.ibphoenix.com

We have answers.

>In regard to limit m, n, are there any cases when the server could avoidYes, there are two cases that come to mind. The first is when

>loading all rows into memory, sorting them, then skipping the first m-1,

>and returning those requested (for instance by using indexes)? (Answers

>for the current optimizer and a hypothetical perfect one both welcome).

the result set is not sorted. The other is when the result set

is sorted in such a way that the engine can read the rows in

index order. At one time, the optimizer (which I've avoided of

late) recognized the special case that FIRST <n> .... SORTED BY

should walk an index (if one exists) rather than retrieving the

whole set and sorting it. However when you get into joins and

result sets that are ordered by terms in different tables, the

optimizer reverts to the find 'em and sort 'em strategy.

Obviously, the cost of the latter (to the engine) is s * i,

where 's' is the size of the total result set and 'i' is the

number of iterations of the query. It's only the cases where

the engine can avoid building the whole result set every time

that my other formula applies ... and my sample computation

was off by a power of ten ... sigh.

Regards,

Ann

www.ibphoenix.com

We have answers.