Re: Resetting Auto Increment?
Posted by:
Rick James
Date: August 27, 2012 08:24AM
One style of such a 'flag':
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT '0'
WHERE ... AND NOT is_deleted ...
That is an extra 1-byte column.
If you have a few thousand rows, there is essentially no performance issue. If you have a million rows, this may be a burden. If 90% of the rows are 'deleted', it will be costly do find the non-deleted rows. Etc.
Moving the 'deleted' items to another table would defeat the goal of permanently preventing duplicate ids.
Another approach is to use the "sequence" example in the manual. It involves a tiny table that is used solely for creating unique, ascending, ids. That would obviate the need for a 'is_deleted' flag or 'deleted' table, but would complicate the creation of a new id slightly.
Subject
Written By
Posted
Re: Resetting Auto Increment?
August 27, 2012 08:24AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.