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 <[email protected]>wrote: > Tks for the tips guys. I'll try both of then. > > > On Thu, Nov 17, 2011 at 6:10 AM, Tomasz Tyrakowski < > [email protected]> 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]
