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…


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

Advertisements


Σχολιάστε

Εισάγετε τα παρακάτω στοιχεία ή επιλέξτε ένα εικονίδιο για να συνδεθείτε:

Λογότυπο WordPress.com

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό WordPress.com. Αποσύνδεση / Αλλαγή )

Φωτογραφία Twitter

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Twitter. Αποσύνδεση / Αλλαγή )

Φωτογραφία Facebook

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Facebook. Αποσύνδεση / Αλλαγή )

Φωτογραφία Google+

Σχολιάζετε χρησιμοποιώντας τον λογαριασμό Google+. Αποσύνδεση / Αλλαγή )

Σύνδεση με %s