Re: Need expert help... a challenging query

An index on (bar, foo) should help in your case because it obviates the need for the sort - instead of selecting rows matching the where clause first and sorting the result set, the database server will be able to scan the index to avoid the sort costs entirely, while filtering out rows which do not meet the foo criteria. In conjunction, if you can use the FIRST clause (I'm more familiar with Informix - where you can), your query will complete really quickly.

create index i1_test on test (bar,foo);

select first 10 * from test
where foo > 50
order by bar desc

Of course, this assumes that the WHERE clause returns lots of rows (which is what you mentioned). If, in fact, the WHERE clause returns very few rows, an index on (foo) would be smarter (because sorting costs are low while access costs would be high without the index).

You could have both indexes and let the cost-based optimizer decide which one to use depending on the actual value of "x" at run-time.

Needless to say, keep in mind that indexes aren't freebies. They can have very significant costs (space, insert/update/delete transactions, etc) in OLTP environments.

HTH
Rudy

Damon Hastings wrote:

> Quick question... how can I make the query "find all records with foo >
> x, sort the results by 'bar', and give me only the top 10" as efficient
> as possible, given that there are millions of records with foo > x?
>
> Do I just build indices for 'foo' and 'bar' and hope for the best? My
> concern there is that the database might use the 'foo' index to do a
> very fast retrieval of everything with foo > x, but then waste a bunch
> of time sorting the entire result set via some n*log(n) sorting routine
> (possibly using the 'bar' index, but still in n*log(n) time) and then
> throw away the bottom 99.999% of the results. Am I correct in assuming
> this is the default algorithm the database would use? If not, then what
> is the default algorithm (or its time complexity, at least); and if so,
> then how do I override it with something that won't waste so much time
> sorting results that will be discarded?
>
> Any help would be much appreciated...
>
> Damon Hastings
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Dec 16 1999 - 08:15:41 CST