[HACKERS] Improved index creation (was:MS SQL features for new version)

[Andreas Pflug]

Rod Taylor wrote:

On Tue, 2004-02-10 at 15:37, Robert Treat wrote:
 

On Tue, 2004-02-10 at 13:20, Rod Taylor wrote:
   

http://www.microsoft.com/sql/yukon/productinfo/top30features.asp
         

Notice the Snapshot Isolation. Sounds like MVCC for MSSQL?
       

Actually, the one I noticed was the ability to add or rebuild indexes on
the fly. That is a pretty slick trick.
     

I was trying to decide how much better this was than 

BEGIN;
DROP INDEX foo ON bar;
CREATE INDEX foo ON bar;
COMMIT;
   

Well.. If thats a big table, you've just blocked selects, updates,
delete, inserts, etc. against that table for the duration of the index
recreation.
 

Just a thought:
Creating an index might be performed as a two step mechanism.
First populate the index, using read-only table data and thus creating a 
preliminary index for a snapshot, maybe performing several rounds.

Next, do the rest, now with locking, to get up-to-date.

This way, the index creation impact on ins/upd/del would be minimized.

Regards,
Andreas


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]