R
Rob Fitzpatrick
Guest
> Am I correct in assuming that using TABLE-SCAN to delete all records from a table has no benefit over using the primary index, since the index entry needs to be removed as well? I would say the answer is "it depends"; TABLE-SCAN might be better or worse than reading with an index. It depends on the state of your table. The benefit of TABLE-SCAN, in theory, is that you don't have to read any index blocks to access the RM blocks in your table. Another benefit is that because it accesses the blocks by following the cluster chain, it will read each RM block into memory just once. The downside of TABLE-SCAN is that it will read all RM blocks in the table, whether or not they contain records. By contrast, a scan via an index will only read RM blocks that contain at least one record. So if this table previously contained many more records than it does now, and it has a very long RM chain, the TABLE-SCAN may read many more RM blocks than the index scan would. However the downside of the index scan is that if the table is heavily scattered and quite large, compared to -B, you might read a given RM block from disk multiple times to delete its records. So without knowing more about your table, it is difficult to say which approach is better. Also, if this is a very large table, it might be faster for you to just dump the table's schema, drop it, and create it again rather than deleting records programmatically. Another alternative approach, if it is in its own area, is to truncate the area.
Continue reading...
Continue reading...