| Subject | Re: [firebird-support] ALTER INDEX causes "attempt to store duplicate value" @L |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2010-02-09T22:38:10Z |
Alec Swan wrote:
former just reads the existing index and computes the selectivity.
The latter deletes and recreates the index, reading all the records,
sorting the key values, and rebuilding the index from the bottom
up. Rebuilding the index can make it more dense, thus more efficient
for reads and (possibly) less efficient for writes. So, from that
point of view, you should choose the path that gets you where you
want to go - just good statistics, or condensing the index.
But, in your case, the ALTER INDEX has turned up a problem, or
at least the appearance of a problem that could cause unexpected
(unwanted and wrong) results of queries.
intuitive and lovely name "IDX_MK2ruDPOuXCeZtnMo6Hg2Q==".
It's got a duplicate value in it, possibly the result of
cosmic radiation, the phase of the moon, or a bad memory chip.
What you should do is figure out which table that index belongs
to - probably encoded in the name - and which fields are the keys.
then a query like this
select <key fields> from <table>
group by <key fields>
having count (*) > 1
will tell you where the duplicates are, and you can decided how
to handle the situation.
Among the bad things that happen when you've got duplicates where
you don't want them is that queries that require a singleton result
fail. And you double count some things. And possible sell the
same cyclotron twice. Bad things...
Good luck,
Ann
> I posted the following message about a week ago and have not receivedWell, SET STATISTICS is much more efficient than ALTER INDEX. The
> any response. Does anybody know what's happening here? Should I avoid
> using "ALTER INDEX" and instead just use "SET STATISTICS" to avoid
> this problem?
former just reads the existing index and computes the selectivity.
The latter deletes and recreates the index, reading all the records,
sorting the key values, and rebuilding the index from the bottom
up. Rebuilding the index can make it more dense, thus more efficient
for reads and (possibly) less efficient for writes. So, from that
point of view, you should choose the path that gets you where you
want to go - just good statistics, or condensing the index.
But, in your case, the ALTER INDEX has turned up a problem, or
at least the appearance of a problem that could cause unexpected
(unwanted and wrong) results of queries.
> FBSQLException: GDS Exception. 335544349.and so on. I assume you've actually got an index withe the
> attempt to store duplicate value (visible to active transactions)
> in unique index "IDX_MK2ruDPOuXCeZtnMo6Hg2Q=="
> Reason: attempt to store duplicate value (visible to active transactions)
> in unique index "IDX_MK2ruDPOuXCeZtnMo6Hg2Q=="
> org.firebirdsql.jdbc.FBSQLException:
> GDS Exception. 335544349. attempt to store duplicate value
> (visible to active transactions)
> in unique index "IDX_MK2ruDPOuXCeZtnMo6Hg2Q=="
intuitive and lovely name "IDX_MK2ruDPOuXCeZtnMo6Hg2Q==".
It's got a duplicate value in it, possibly the result of
cosmic radiation, the phase of the moon, or a bad memory chip.
What you should do is figure out which table that index belongs
to - probably encoded in the name - and which fields are the keys.
then a query like this
select <key fields> from <table>
group by <key fields>
having count (*) > 1
will tell you where the duplicates are, and you can decided how
to handle the situation.
Among the bad things that happen when you've got duplicates where
you don't want them is that queries that require a singleton result
fail. And you double count some things. And possible sell the
same cyclotron twice. Bad things...
Good luck,
Ann