T
Tim Kuehn
Guest
The issue is which direction you're going. Finding a block using the primary index means taking the data, looking it up in the index, then reading the block that goes with that index. When you delete a record the engine has to delete all the index entries for all the indexes that go that record. Depending on where the blocks are in the index will govern how fast this process can run. Finding a block using table-scan avoids that by going record by record in order by physical record and skipping the index-lookup/block load part of the process. Deleting a record would only incur an index entry lookup / deletion. Since those are linked by a pointer, there's not much time used in finding a block with the target record. As an experiment do a FOR EACH on a big table using a) a unique index, B a non-unique index, then c)TABLE-SCAN and take timing measurements. You should find that C is the fastest, followed by B, then A.
Continue reading...
Continue reading...