הבלוג של גרי רשף

13/04/2010

שימוש ב-Partitioned Tables

Filed under: Uncategorized — תגיות: — גרי רשף @ 14:06

Partitioned Tables הוא שמה של הטכנולוגיה המאפשרת לחלק טבלה לפי תחומים למספר חלקים,
ובכך להקל על השימוש.
דוגמה אופיינית יכולה להיות חלוקה של טבלת המכירות לפי שנים – כך שלכל שנה יהיה Partition משלה, או לפחות שלשנה השוטפת שמולה נעשית עיקר העבודה יהיה Partition משלה.
חיפוש ושינוי נתונים צפוי להיות אז יעיל ומהיר יותר,
ובנוסף ניתן בקלות לצרף טבלאות אחרות כ-Partition-ים חדשים,
או להפוך Partition-ים קיימים לטבלאות חיצוניות,
וכל זה מבלי "לשלם" במחיר של יבוא ויצוא של נתונים שנועלים טבלאות ומכבידים על הביצועים.

ניצור דטבייס חדש לצורך ההדגמה:

Use tempdb

Go

Create Database PartDB;

Go

Use PartDB;

Go

כעת ניצור את התשתית לשימוש ב-Partitions:

Partition Function – קובעת לפי איזה שדה תתבצע החלוקה, ומה יהיו נקודות החלוקה. במקרה זה החלוקה תהיה לשני תחומים כך שכל הנתונים עד סוף 2002 הם נתונים הסטוריים, ומ-2002 ואילך נתונים עדכניים.

Partition Scheme – קובעת היכן יאוחסן כל Partition של הפונקציה הנ"ל. בשלב זה שניהם יהיו על ה-Filegroup הראשי, כלומר- Primary:

CREATE PARTITION FUNCTION PART_FN (datetime)

AS RANGE LEFT FOR VALUES ('20021231');

Go

CREATE PARTITION SCHEME PART_SCH

AS PARTITION PART_FN TO ([Primary],[Primary]);

Go

ניצור טבלה לפי סכימת החלוקה הנ"ל ונכניס לתוכה נתונים מטבלה ב-AdventureWorks:

CREATE TABLE CurrencyRate(CurrencyRateID int,

                        CurrencyRateDate datetime,

                        FromCurrencyCode nchar(3),

                        ToCurrencyCode nchar(3),

                        AverageRate money,

                        EndOfDayRate money,

                        ModifiedDate datetime) ON PART_SCH (CurrencyRateDate);

GO

Insert Into CurrencyRate

Select    * 

From    AdventureWorks.Sales.CurrencyRate T 

Order By T.CurrencyRateDate;

Go

* נוכל לעיין בנתוני הטבלה ולראות לאיזה משני ה-Partitins הופנתה כל שורה:

SELECT    $PARTITION.PART_FN(CurrencyRateDate) Partition,

        MIN(CurrencyRateDate) [Min],

        MAX(CurrencyRateDate) [Max],

        COUNT(CurrencyRateDate) [Count]

FROM    CurrencyRate

Group By $PARTITION.PART_FN(CurrencyRateDate)

Order By $PARTITION.PART_FN(CurrencyRateDate);

Go

נגדיל כעת את מספר ה-Partitions וניצור חדש שיכיל את נתוני 2004 ואילך על ידי שינוי הפונקציה והסכימה.

בנוסף, ניצור עבורו Filegroup חדש על דיסק אחר (בניגוד לשני הנ"ל ששוכנים על ה-Primary) משיקולים של ביצועים:

ALTER DATABASE PartDB

ADD FILEGROUP FGCurrFile

Go

ALTER DATABASE PartDB

ADD FILE(NAME = CurrFile,FILENAME = 'E:\CurrFile.ndf',SIZE = 1MB)

TO FILEGROUP FGCurrFile

GO

ALTER PARTITION SCHEME PART_SCH

NEXT USED [FGCurrFile]

GO

ALTER PARTITION FUNCTION PART_FN() 

SPLIT RANGE ('20031231')

GO 

כדאי לשוב לאחר כל שינוי כזה לשליפה *הנ"ל כדי לבחון את השינוי.

התחרטנו? ניתן לאחד חזרה את שני ה-Partitions ולבטל את ה-Filegroup שיצרנו:

ALTER PARTITION FUNCTION PART_FN()

MERGE RANGE ('20031231')

GO

ALTER DATABASE PartDB Remove FILE CurrFile;

Go

ALTER DATABASE PartDB Remove FILEGROUP FGCurrFile

Go

כעת ניפטר מחלק מהנתונים- נהפוך את ה-Partition הישן לטבלה נפרדת, שנוכל לטפל בה מבלי להשפיע על הטבלה הקיימת,

ולאחר מכן נבדוק כמה שורות יש בכל טבלה כדי להיווכח שהנתונים עברו:

CREATE TABLE CurrencyRate_Out(CurrencyRateID int,

                        CurrencyRateDate datetime,

                        FromCurrencyCode nchar(3),

                        ToCurrencyCode nchar(3),

                        AverageRate money,

                        EndOfDayRate money,

                        ModifiedDate datetime);

Go

ALTER TABLE dbo.CurrencyRate

SWITCH PARTITION 1

TO dbo.CurrencyRate_Out;

Go

Select COUNT(*) [Count] From CurrencyRate;

Go

Select COUNT(*) [Count] From CurrencyRate_Out;

ולסיום נבצע את התרגיל ההפוך- ניצור טבלה חדשה עם נתונים משנת 2005, ונצרף אותה לקיימת כ-Partition חדש.

במקרה זה יש להוסיף לטבלה החדשה מגבלה שתבטיח שהנתונים בה מתאימים ל-Partition החדש, וכמובן- ליצור אותו (את ה-Partition).

בדוגמה הבאה נוסיף שורה סימלית אחת לטבלה, ונוודא לאחר השינוי שהיא בטבלה הקיימת ("הטבלה המפורטשת" בלשון הבראנז'ה):

CREATE TABLE CurrencyRate_In(CurrencyRateID int,

                        CurrencyRateDate datetime CHECK (CurrencyRateDate >= '20050101' AND CurrencyRateDate IS NOT NULL),

                        FromCurrencyCode nchar(3),

                        ToCurrencyCode nchar(3),

                        AverageRate money,

                        EndOfDayRate money,

                        ModifiedDate datetime);

Go

Insert Into CurrencyRate_In Values(15000,'20050101','USD','ARS',0.99,0.999,'20050101');

Go

ALTER PARTITION SCHEME PART_SCH

NEXT USED [Primary]

Go

ALTER PARTITION FUNCTION PART_FN() 

SPLIT RANGE ('20041231');

Go

ALTER TABLE CurrencyRate_In switch TO CurrencyRate PARTITION 3;

Go

Select * From CurrencyRate_In;

Select * From CurrencyRate Where $PARTITION.PART_FN(CurrencyRateDate)=3

בהצלחה!

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

הזינו את פרטיכם בטופס, או לחצו על אחד מהאייקונים כדי להשתמש בחשבון קיים:

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: