Efficient Schema Changes II

Σε προηγούμενο post Efficient schema changes είχα αναφέρει το πόσο χρονοβόρα μπορεί να γίνει μια διαδικασία αλλαγής του schema ενός πίνακα, όταν αυτός είναι γεμάτος data. Ας δούμε μια περίπτωση. Το σενάριο είναι το εξής: Έχουμε έναν πίνακα με ένα πεδίο σταθερού μεγέθους το οποίο θέλουμε να μειώσουμε ώστε να χωρέσουν περισσότερες εγγραφές στα data και index pages.

Εκ πρώτης όψεως δεν φαίνεται δύσκολο κάτι τέτοιο. Μπορούμε να γράψουμε ένα ALTER TABLE … ALTER COLUMN … statement και να ξεμπερδέψουμε. Σπάνια όμως θα παίξει κάτι τέτοιο (δείτε στα Books On Line τους περιορισμούς). Και αν παίξει, αυτό που θα γίνει θα είναι να κλειδώσει ολόκληρο τον πίνακα όσο γίνεται η διαδικασία, όπερ σημαίνει ότι ως λύση δεν είναι και τόσο ελκυστική.

Εναλλακτικά, μπορεί να γίνει αυτό που κάνει το Management Studio/Enterprise Manager όταν κάνουμε αυτή τη δουλειά μέσω του UI. Δημιουργία ενός νέου temp πίνακα με το νέο schema, μετά INSERT INTO από τον παλιό στο νέο, drop του παλιού, rename του νέου με το παλιό όνομα. Ρίξτε μια ματιά στο INSERT INTO που παράγεται όταν ζητήσουμε αλλαγή του πεδίου Country σε nvarchar(5) από nvarchar(15) (δεν είναι σταθερού μήκους και ούτε indexed αλλά δεν πειράζει, ως παράδειγμα κάνει)

IF EXISTS(SELECT * FROM dbo.Customers)
  EXEC('INSERT INTO dbo.Tmp_Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, ModifiedDate)
  SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, CONVERT(nvarchar(5), Country), Phone, Fax, ModifiedDate FROM dbo.Customers WITH (HOLDLOCK TABLOCKX)')
GO

Χμμμμ… WITH (HOLDLOCK TABLOCKX) (!!!) Μαζί με τη διαχείριση των constraints (drop τα παλιά, create νέα στον temp πίνακα, κλπ) όλα αυτά σε ένα transaction. Όχι πολύ efficient αλλά ευτυχώς που παράγεται αυτό το script αυτόματα!

Ας δούμε μια άλλη προσέγγιση στο πρόβλημα:

Προσθέτουμε στον υπάρχον πίνακα ένα νέο column με την αλλαγή που θέλουμε, κάνουμε UPDATE (αντί INSERT INTO) με  τις τιμές από το παλιό πεδίο, κάνουμε drop το παλιό πεδίο και rename το νέο με το παλιό όνομα. Το πλεονέκτημα είναι ότι δεν χρειάζεται table lock πράγμα που σημαίνει ότι αυξάνεται το availability του πίνακα ενώ με το κολπάκι που θα σας δείξω για το update, μπορεί να τρέξει η διαδικασία σε ένα σύνολο από πολλά μικρά transactions.



USE Northwind

ALTER TABLE Customers
ADD tmpCountry NVARCHAR(5) NULL

SET NOCOUNT ON
SET ROWCOUNT 500

DECLARE @rows INT
SELECT  @rows = 1
WHILE @rows > 0
    BEGIN
        BEGIN TRAN
        UPDATE  Customers
        SET     tmpCountry = CONVERT(NVARCHAR(5), Country)
        WHERE   tmpCountry IS NULL
        SET @rows = @@ROWCOUNT
        COMMIT
    END

ALTER TABLE Customers DROP COLUMN Country

EXEC sp_rename 'Customers.tmpCountry', 'Country', 'COLUMN'

To κόλπο είναι ότι όταν γίνεται ADD το πεδίο tmpCountry, αυτόματα μπαίνει η τιμή NULL σε όλες τις εγγραφές. Κατόπιν, το loop τρέχει όσο βρίσκει εγγραφές με null τιμές για να τους περάσει μέσω του UPDATE τη νέα τιμή. Αυτή η δουλειά γίνεται κάθε φορά σε batches των 500 εγγραφών, δηλαδή δεν χρειάζεται να κλειδώνεται ολόκληρος ο πίνακας. Αν θέλουμε, μπορούμε να προστατέψουμε τις εγγραφές που έχουν γίνει ήδη update από τυχόν updates που έρχονται από τις εφαρμογές που χρησιμοποιούν τη βάση βάζοντας προσωρινά ένα update trigger για το πεδίο Country. Αυτό το update trigger θα κάνει propagate τις αλλαγές στο νέο πεδίο του πίνακα. Το μόνο που χρειάζεται πλέον είναι να κάνουμε το switch των constraints…

 Υ.Γ. Τα παραπάνω, βασίζονται σε υλικό που άντλησα από εδώ, πηγή μεγάλης βοήθειας όταν παιδευόμουν...