Dne 26.03.2010 11:02, Svein Erling Tysvær napsal(a):

> What about
>
> SELECT distinct aa.AREA_ID
> FROM
> TMP_CKRAJ aa
> WHERE
> EXISTS(SELECT 1 FROM TMP_CKRAJ tc WHERE
> tc.ID_CCOUNTRY = 'CZE' and
> tc.NAME_TYPE = 'B' and
> tc.AREA_ID = aa.AREA_ID and
> tc.rdb$db_key> aa.rdb$db_key)
>
>

I'm not familiar enough with rdb$db_key magic, so I can't say what it
means in this query. I'm just guessing that it works like my HAVING
clause (every record has unique db_key).
Everything will be much simpler if Firebird will be able to produce more
detailed execution plans (show us columns that are used from an index).
Hopefully it will be released in 2.5 final :)

M.

> I just guess that's similar to what Arno intended to write. Without the GROUP BY you'll either need the distinct or add a further NOT EXISTS (distinct is simpler).
>
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Milan Tomeš - Position
> Sent: 26. mars 2010 10:56
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Index suggestion
>
> Hi,
>
> Dne 26.03.2010 10:39, Arno Brinkman napsal(a):
>
>> Hi,
>>
>>
>>
>>> SELECT DISTINCT AREA_ID
>>> FROM TMP_CKRAJ
>>> WHERE
>>> (ID_CCOUNTRY = 'CZE') AND
>>> (NAME_TYPE = 'B')
>>> GROUP BY AREA_ID
>>> HAVING
>>> (COUNT(AREA_ID)> 1)
>>>
>>> It's easy to make an index for ID_CCOUNTRY and NAME_TYPE, but is there
>>> any chance to help engine with index that can be used for DISTINCT/GROUP
>>> BY/HAVING COUNT ?
>>>
>>>
>> Compound index on (ID_CCOUNTRY, NAME_TYPE) looks most usefull to me, but i don't see the usage of DISTINCT in your query
>> here.
>>
>>
> You're right. There is no reason to use DISTINCT in that query. Multiple
> AREA_IDs are eliminated by GROUP BY clause.
>
>
>> Also think of the next possible approach for your query:
>>
>> SELECT
>> aa.AREA_ID
>> FROM
>> Table_All_Area aa
>> WHERE
>> EXISTS(SELECT 1 FROM TMP_CKRAJ tc WHERE
>> tc.ID_CCOUNTRY = 'CZE' and
>> tc.NAME_TYPE = 'B' and
>> tc.AREA_ID = aa.AREA_ID)
>>
>>
>>
> I guess this will not work. This will select all AREA_IDs that exists,
> but I need only AREA_IDs that exists more than once (in next step I'm
> updating NAME_TYPE to value 'E' to get only one record for every
> condition AREA_ID = xxx and NAME_TYPE = 'B'). I don't have a table with
> distinct values of AREA_IDs.
>
>