Sean,

W dniu 03.05.2013 21:42, Leyne, Sean pisze:

> Marcin,
>
>> W dniu 03.05.2013 19:18, Leyne, Sean pisze:
> ...
>
>>> For this query, a DESCENDING compound index with the ID and
>> WORK_DATE would be best.
>>>
>> Are you sure that it should be ID not SITE_ID?
>
> Sorry, typo!
>
> Yes, it should be SITE_ID
>
>
>> I've created DESCENDING index for SITE_ID, WORK_DATE and it looks really
>> fast despite fact that all records have been read (according to IB Expert
>> Performance Analisys).
>
> That is not what I would expect. (It should have read 1 row per SITE_ID to confirm that the "max" value was valid for the candidate row).
>
> What is the selectivity/distribution of values in your table?
>

Test table contains 3264 records
Selectivity of the index is 0.00031706
and details:

Depth: 2, leaf buckets: 20, nodes: 3264
Average data length: 19.23, total dup: 110, max dup: 3
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 19

Marcin