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

08/05/2011

ההבדל בין Unique Index ו-Unique Constraint

Filed under: Uncategorized — תגיות: , , , — גרי רשף @ 17:17

בפורום בסיסי נתונים בתפוז נשאלה שאלה כיצד להגדיר עמודה שאינה מפתח כ-Unique (כלומר- ללא ערכים כפולים).
עניתי- "בעזרת "Unique Index. זמן קצר לאחר מכן עדי הוסיף שאפשר להשתמש גם ב-Unique Constraint, ויש לי הרגשה שהוא ציין זאת לא בתור "גם לי יש מה לומר" אלא כי יש בהצעתו יתרון מסויים.
כדי לעמוד על ההבדלים בין שתי ההצעות (לא הרבה..), נתחיל מהדומה, וניצור לשם כך טבלה עם שתי עמודות זהות שלאחת נגדיר אינדקס ולשניה אילוץ:

If Object_Id('Try001','U') Is Not Null Drop Table Try001;
Go

Create Table Try001(ID Int Identity,
                    I1 Int Null,
                    I2 Int Null);
Go

Alter Table Try001 Add Constraint Try001_I1_Constraint Unique Nonclustered(I1)
Create Unique Index Try001_I2_Index ON Try001(I2);
Go

With T As
(Select 1 N
Union All
Select  N+1 N
From    T
Where   N<100)
Insert
Into    Try001
Select  N,
        N
From    T;
Go

clip_image002

מתברר שהמערכת יצרה לאילוץ אינדקס מתאים, זהה לאינדקס שנוצר באופן ישיר (ובנוסף מציגה את האינדקס שנוצר ולא את האילוץ) וזה די הגיוני: לא סביר לצפות שבכל פעם שמכניסים או משנים ערך בעמודה – המערכת תבצע Scan על כל הטבלה כדי לבדוק שהחדש אינו קיים, ואינדקס הוא כלי עזר מתבקש במקרה זה.

ננסה להכניס ערכי Null לעמודות:

Insert
Into Try001
Values(Null,0);
Go

Insert
Into Try001
Values(0,Null);
Go

ה-Nulls נכנסו ללא בעיות שכן שתי העמודות הוגדרו כ-Nullable.

מה יקרה אם ננסה לבצע שליפות מפולטרות או ממויינות של העמודות?

Select I1 From Try001 Where I1=1;
Select I2 From Try001 Where I2=1;

clip_image004

Select I1 From Try001 Order By I1;
Select I2 From Try001 Order By I2;

clip_image006

בשני המקרים נעשה שימוש באינדקסים הרלונטיים בפעולות Seek ו-Scan.

האם יש הבדל בסקריפטים שהמערכת יוצרת לכל אחד מהאובייקטים הללו?

ALTER TABLE [dbo].[Try001] ADD CONSTRAINT [Try001_I1_Constraint] UNIQUE NONCLUSTERED([I1] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [Try001_I2_Index] ON [dbo].[Try001]([I2] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

המערכת "זוכרת" שהראשון הוא אילוץ והשני אינדקס, אבל הפרמטרים זהים – למעט Drop_Existing (ביטול האינדקס הקיים – אם יש).

עד כאן – הכל אותו הדבר, ונעבור להבדלים.

כדאי לשים לב שאילוץ (Constraint) הוא כלי לוגי שנועד לממש לוגיקה עסקית, והדרישה אליו תבוא מן הסתם ממנתח המערכת;

ואינדקס הוא כלי פיזי שנועד לשפר ביצועים, והדרישה אליו תבוא מה-DBA.

מכאן שהדרך הראויה היא להשתמש ב-Constraint, ולהשאיר למערכת את החופש להחליט כיצד לממש זאת באופן היעיל ביותר (כנראה שזה ימשיך להיות אינדקס בעתיד הנראה לעין..).

יחד עם זאת- מכיוון שבכל מקרה נוצר אינדקס, ומכיוון שכולנו חכמים וכולנו נבונים וכולנו יודעים את התורה- יתכן ולו אנחנו היינו מגדירים את האינדקס – היינו עושים זאת טוב יותר מאשר המערכת שבחרה בברירות המחדל השונות; וראוי לשקול זאת במקרים מיוחדים. למשל- ביצירת Unique Constraint אין לנו דרך להגדיר Fill Factor לאינדקס או להוסיף לו עמודות (Covered Index) על ידי Include כדי שיוכל לשמש כאינדקס למטרות נוספות (חוץ מבדיקת Uniqueness), ובנוסף- מכיוון שלא יצרנו את האינדקס במכוון – אנו עלולים לשכוח לתחזק אותו כמו את שאר האינדקסים.

כדאי בנוסף לשים לב למקרה המעניין הבא: ביצירת Constraints ניתן להשתמש באופציית With NoCheck שגורמת לכך שהאילוץ מופעל רק על הנתונים המוכנסים מכאן ואילך, אך לא על מה שכבר הוכנס.

נערוך ניסוי קצר- ניצור טבלה דומה לקודמת, ונכניס לה מספר ערכים כפולים לפני הגדרת האילוץ והאינדקס:

If Object_Id('Try002','U') Is Not Null Drop Table Try002;
Go

Create Table Try002(ID Int Identity,
                    I1 Int Null,
                    I2 Int Null);
Go

Insert
Into Try002
Select 0,0
Union All
Select 0,0;
Go

נגדיר אילוץ Unique עם אופציית NoCheck:

Alter Table Try002 WITH NOCHECK Add Constraint Try002_I1_Constraint Unique Nonclustered(I1);
Go

clip_image008

אופס: הודעת שגיאה!

די הגיוני שתופיע שגיאה- לא ניתן להגדיר אינדקס Unique כך שיתעלם ממה שכבר קיים, וגם אם היו רוצים ליצור אופציה כזו – לא לגמרי ברור מה הייתה הכוונה: מעתה יהיה אסור להכניס ערכים שכבר הוכנסו (אסור להכניס עוד 0 כי כבר יש)? יהיה מותר להכניס ערכים קיימים פעם אחת ובדיקת הכפילות תתבצע רק עליהם (מותר להכניס עוד 0 אחד למרות שכבר יש)?

ביצירת אינדקס ידני ניתן להתמודד עם הבעייה- אם ניצור Filtered Index כך שיופעל רק על ערכים עתידיים, כמובן בתנאי שיש לנו עמודת Identity או תאריך שמאפשרת זאת:

Create Unique Index Try002_I2_Index On Try002(I2) Where ID>3;
Go

כעת שני ערכי 0 הקיימים יוכלו להישאר,
אך לא ניתן יהיה להכניס עוד ערכים כפולים.

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

להגיב »

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

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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