Hi



A descending index is perfect to get max().



But in your case, you want to have the max of a subset. Try using a
index on foreign_key only.



Best regards

Christian





Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] Im Auftrag von tomjanczkadao
Gesendet: Mittwoch, 18. August 2010 11:16
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] query optimization- MAX() from .. WHERE





Hi
I've got table similar to the following example:

CREATE TABLE TEST_TAB (
ID INTEGER NOT NULL PRIMARY KEY,
NUMER INTEGER,
FOREIGN_KEY INTEGER);
CREATE DESCENDING INDEX TEST_TAB_IDX1 ON TEST_TAB (FOREIGN_KEY, NUMER);

and query like this:

select max(t.numer)
from test_tab t
where t.foreign_key = :some_value

PLAN (T INDEX (TEST_TAB_IDX1))

So it uses proper index but Performance Analysis shows lot of reads (as
much as count() where foreign_key = :some_value).

Is it any way to improve this query performance

Regards, Tomek





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