Ann, Milan,

You would have to be pretty crazy to attempt such a SP that I proposed
without a separate descending index on the ID column, which would make
it significantly faster than a double query. Actually, that is a bit
harsh, if you don't understand Firebird has directional indices, then
you could also make the assumption it could use the PK index, but
Milan you are right, if you do not provide a descending index then a
natural scan will be faster than using the PK index, so I would hope
the optimiser didn't bother trying to use it.

Unfortunately, he is unable to use the SP approach (contacted me off
list) as he needs to maintain compatibility with MySQL.

I think the double query idea may have merit, given that the only
other work around is to query the id's back to the client application
and fire off a parametised query in descending order (essentially what
the SP does except you then potentially have a lot more comms overhead).

If the PK field allows negatives (ours are controlled by a domain that
enforces > 0, but if your definition allows negatives), the following
code may be "safer" in the sense that you do not have to find a number
"big enough" to ensure you get a gap.

update tableA
set ID = (-1 * ID);

update tableA
set ID = (-1 * ID) + 1;

Normally, I don't play around with the ID value of an entity, but I
suppose the same problem could occur with any column declared unique.

Adam


--- In firebird-support@yahoogroups.com, Milan Babuskov <albis@e...>
wrote:

> Ann W. Harrison wrote:
> > The PK search is quick
>
> Generally, yes. But in this case it has to be done for *each* record.
> AFAIU, that means that it has to locate each record. A simple "natural"
> update also touches all records, but it doesn't even bother with index
> so it should be quicker. Right?
>
> --
> Milan Babuskov
> http://fbexport.sourceforge.net