On 5/2/2014 3:25 PM, Vlad Khorsun wrote: >> OK, the alternative to record lookups is to store the transaction id in >> index. This would require an index insertion for all indexes defined on >> a table even if the key value didn't change. It would also require a >> corresponding index deletion for each index defined on the table when a >> record version was garbage collected. The update performance would go >> straight down the toilet. And this doesn't include the performance drop >> due to fluffed up indexes. >> >> If you have a third alternative, by all means share it. > Mark every index key with two tx numbers: > - first, mandatory - is the number of tx that inserts this key (insert, > update when key was changed), > - second, optional - is the number of tx that deletes this index key (delete, > update when key was changed). > > - inserts will cost the same as now, > - updates will > - if key was not changed - same cost as now (zero) > - if key was changed - twice cost as now (mark old key with current tx > number, insert new key) > - delete will have additional cost to mark old key with current tx number > - undo of update and delete must additionally clear the mark for the old key > - index keys will be more wide than now, there is some tricks to reduce new > index keys length > - garbage collection will be the main winner - there is no need to process > indices at the same time > as table records. It allows to process every index independent from table > and almost completely > eliminates random IO when records are removed. Currently, when table have > more than 5-6 indices, > garbage collection is terrible slow because of random IO. > - selects will have no need to read record version to evaluate record > visibility > - also it allows to have index coverage (also requires to use such index key > encoding which allows > to recover original value from index key) > I'm thinking. I'll get back later.
------------------------------------------------------------------------------ "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available. Simple to use. Nothing to install. Get started now for free." http://p.sf.net/sfu/SauceLabs Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
