Re: Improving the Performance of Full Table Updates

Hi Tom,
      Thanks for the feedback. Let me clarify my intention again.
This was thought for improving the performance of the Data Warehousing applications

Full table updates similar to
"Update dd set n2=n2+1"

When you talked about firing triggers, i looked into the implementation of triggers and the approach i suggested may not work fine with the Triggers. Since we cannot hold write locks and fire triggers, triggers should get disabled for this. But Remember in Data Warehousing applications, people won't be usually having Row-level triggers.

My alternate suggestion would be to make this kind of update an optional one to provide the speed up.

If a page contains 100 rows, then the current scenario takes 1 read lock + 100 write locks to complete the full table update. In the suggested scenario, it takes one Write Lock. Also it reduces the 101 Logical I/Os to1 Logical I/O. This might provide the same kind of benefit the Bitmap Index Scan provided.

Again there are some specific cases
a) If the tuple is locked / Concurrently being updated:
                    Currently we release the buffer, take a lock on the tuple and wait for the transaction to complete in case of concurrent updates. In the Full table update also, we will do the same.

b) If the page contains lot of deleted tuples:
                     This is a tricky scenario. Say if we have 100 tuples and we have 20% of them deleted. In the current scenario, we will find that out during the read lock and we will not waste time in those tuples during the write lock. But in the suggested scenario, we will be wasting time in those with the write lock on the buffer. In order to circumvent that, we can resort to a one read lock + one write lock combination.

Again if this full table updates are thought with the OLTP applications in mind, then this is not at all a suitable option. This will only benefit the people with Data Warehouses.

Expecting some more replies....


Thanks,
Gokul.