Hi guys,

Since the source database is still in production, i'm trying at max to
avoid running a gfix (although this seems to be inevitable) to avoid any
downtime.

Anyway, i did follow Svein tip and tried

SELECT MyIndexedField+0, count(*) FROM MyTable GROUP BY 1 HAVING count(*) >
1

instead of

SELECT MyIndexedField, count(*) FROM MyTable GROUP BY 1 HAVING count(*) > 1


AFAIK, adding the '+0' really tricks the planner and force a table scan.
Would still worth a try to run a gfix or this test exclude any chance of
having a page failure? The odd thing is that in the restored database i
have not one, but at least 10 dup keys in more than one table.

On Thu, Nov 17, 2011 at 7:44 AM, Leonardo Carneiro
<chesterman86@...>wrote:

> Tks for the tips guys. I'll try both of then.
>
>
> On Thu, Nov 17, 2011 at 6:10 AM, Tomasz Tyrakowski <
> t.tyrakowski@...> wrote:
>
>> My point exactly. That's why I adviced checking for duplicates _after_
>> removing indices and primary keys on problematic tables. I've never
>> tried the method proposed by Svein, but if it really omits indices, it's
>> better than mine (more subtle and doesn't mess with metadata).
>>
>> regards
>> Tomasz
>>
>> On 2011-11-17 08:10, Svein Erling Tysvær wrote:
>> >> Hi Tomasz. I already checked the source database for duplicated
>> entries,
>> >> and found none, but i did not tried to verify the database with gfix.
>> >
>> > Exactly how did you check it for duplicate entries? The point is that
>> if there's a problem with an index or key, then doing
>> >
>> > SELECT MyIndexedField, count(*)
>> > FROM MyTable
>> > GROUP BY 1
>> > HAVING count(*)> 1
>> >
>> > will use the index/key and not discover the duplicate entry, whereas
>> >
>> > SELECT MyIndexedField+0, count(*)
>> > FROM MyTable
>> > GROUP BY 1
>> > HAVING count(*)> 1
>> >
>> > cannot use the index and will find such duplicates.
>> >
>> > HTH,
>> > Set
>> >
>>
>>
>> --
>> __--==============================--__
>> __--== Tomasz Tyrakowski ==--__
>> __--== SOL-SYSTEM ==--__
>> __--== http://www.sol-system.pl ==--__
>> __--==============================--__
>>
>>
>> ------------------------------------
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Visit http://www.firebirdsql.org and click the Resources item
>> on the main (top) menu. Try Knowledgebase and FAQ links !
>>
>> Also search the knowledgebases at http://www.ibphoenix.com
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> Yahoo! Groups Links
>>
>>
>>
>>
>

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