Hi Dmitry;

As the query never finished (I tried 3 times, aborting all of them
after 15 minutes), I added a new where clause (m1."FECH"
between '20080201' and '20080330') in the first place, to make it
working with a shorter set of rows.

Now my indexes are (Flamerobin report):

MOVI_FECH ASC FECH, HORA 0.000007
MOVI_FECH_DESC DESC FECH 0.000186
MOVI_HECH ASC HECH 0.333333
MOVI_MIEM ASC MIEM 0.030303
MOVI_PK ASC MOVI 0.000000
MOVI_PRES ASC PRES 1.000000
MOVI_PROC ASC PROC 0.000077
MOVI_TIPO ASC TIPO 0.166667

As you will see (TEST #3) the max condition will work for just 6600
rows, and for that it needs 22 seconds (TEST #0), returning 218 rows:

TEST #0:
(total: 218 rows)

Preparing query: select m1."PROC",m1."FECH",m1."HORA",m1."DSCR"
from "MOVI" m1 where (m1."FECH" between '20080201' and '20080330')
and m1."TIPO" = '1' and m1."HECH" = 'P' and m1."FECH"=(select max
(m2."FECH") from "MOVI" m2 where m1."PROC"=m2."PROC" and m2."TIPO"
= '1' and m2."HECH" = 'P')
Prepare time: 00:00:00.
Field #01: MOVI.PROC Alias:PROC Type:STRING(10)
Field #02: MOVI.FECH Alias:FECH Type:STRING(8)
Field #03: MOVI.HORA Alias:HORA Type:STRING(4)
Field #04: MOVI.DSCR Alias:DSCR Type:STRING(200)
PLAN (M2 ORDER MOVI_FECH_DESC INDEX (MOVI_PROC))
PLAN (M1 INDEX (MOVI_FECH_DESC, MOVI_TIPO))


Executing...
Done.
363812 fetches, 0 marks, 19905 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 49845 index, 0 seq.
Delta memory: 13312 bytes.
Execute time: 00:00:22.
Script execution finished.


TEST #1:
(total: 21223 rows)

Preparing query: select m1."PROC",m1."FECH",m1."HORA",m1."DSCR"
from "MOVI" m1 where (m1."FECH" between '20080201' and '20080330')
Prepare time: 00:00:00.
Field #01: MOVI.PROC Alias:PROC Type:STRING(10)
Field #02: MOVI.FECH Alias:FECH Type:STRING(8)
Field #03: MOVI.HORA Alias:HORA Type:STRING(4)
Field #04: MOVI.DSCR Alias:DSCR Type:STRING(200)
PLAN (M1 INDEX (MOVI_FECH_DESC))

Executing...
Done.
1074 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 520 index, 0 seq.
Delta memory: 96 bytes.
Execute time: 00:00:00.
Script execution finished.

TEST #2: Same than TEST #1 but adding: and m1."TIPO"='1'
(total: 17830 rows)

Preparing query: select m1."PROC",m1."FECH",m1."HORA",m1."DSCR"
from "MOVI" m1 where (m1."FECH" between '20080201' and '20080330')
and m1."TIPO"='1'
Prepare time: 00:00:00.
Field #01: MOVI.PROC Alias:PROC Type:STRING(10)
Field #02: MOVI.FECH Alias:FECH Type:STRING(8)
Field #03: MOVI.HORA Alias:HORA Type:STRING(4)
Field #04: MOVI.DSCR Alias:DSCR Type:STRING(200)
PLAN (M1 INDEX (MOVI_FECH_DESC, MOVI_TIPO))

Executing...
Done.
1896 fetches, 0 marks, 880 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 528 index, 0 seq.
Delta memory: 83864 bytes.
Execute time: 00:00:00.
Script execution finished.

TEST #3: same as TEST #2 but adding: and m1."HECH"='P'
(total: 6600 rows)

Preparing query: select m1."PROC",m1."FECH",m1."HORA",m1."DSCR"
from "MOVI" m1 where (m1."FECH" between '20080201' and '20080330')
and m1."TIPO"='1' and m1."HECH"='P'
Prepare time: 00:00:00.
Field #01: MOVI.PROC Alias:PROC Type:STRING(10)
Field #02: MOVI.FECH Alias:FECH Type:STRING(8)
Field #03: MOVI.HORA Alias:HORA Type:STRING(4)
Field #04: MOVI.DSCR Alias:DSCR Type:STRING(200)
PLAN (M1 INDEX (MOVI_FECH_DESC, MOVI_TIPO))

Executing...
Done.
3028 fetches, 0 marks, 1230 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 1094 index, 0 seq.
Delta memory: 408 bytes.
Execute time: 00:00:00.
Script execution finished.

BTW ... why FB does not use MOVI_HECH, when it is clear it makes a
big diference, specialy regarding that the whole tables is more than
700,000 rows, and MOVI_HECH is 33% ???

Thank you.

Dany