none
שימוש ב view indexes לשאילתה מורכבת עם case שקשור לרשומות מטבלאות אחרות RRS feed

  • שאלה

  • שלום לחברי הפורום. התחלתי ללמוד על הנושא, והבנתי כי בעת יצירת אינדקס על view , כל גוף התוצאה נשמר באינדקס עצמו. רציתי להבין, כיצד ה SQL יודע להבחין בשינויים הנעשים בשורות הטבלאות המצריכות לחשב מחדש שורה בשאילתא.

    למשל, נניח שהשאילתא מכילה התניית case , אשר פונה לטבלת עזר, ולפי זה הערך מחושב. למשל:

    select t1.id,t1.code,(
      case when exists(select * from tbl2 where code=t1.code) then 1 else 2  end) v1
    from tbl1 t1

    השאלה היא מה הגבול שבו ה SQL יודע לבצע זאת. או איך אוכל לבדוק אם הוא עושה זאת?

    יום חמישי 13 מרץ 2014 18:40

תשובות

  • שבת שלום,

    השאלה מנוסחת בצורה שלא כל כל ברורה לי. אני אנסה לענות על מה שאני מבין\

    בעת יצירת אינדקס על view , כל גוף התוצאה נשמר באינדקס עצמו

    הרעיון של אינדקס הוא ליצור מיפוי של הנתונים. לפעמים גם הנתונים עצמם נשמרים ולפעמים רק הפנייה pointer בהתאם לסוג האינדקס למשל.

    כיצד ה SQL יודע להבחין בשינויים הנעשים בשורות הטבלאות המצריכות לחשב מחדש שורה בשאילתא.

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

    למשל, נניח שהשאילתא מכילה התניית case , אשר פונה לטבלת עזר, ולפי זה הערך מחושב.

    אין שום קשר בין שאילתת SELECT ובין בימצוע חישוב מחדש. אינדקס לא משתנה בזמן שאילתת בחירה אלא בזמן שאילתות שמשנות את הנתונים הקשורים לאינדקס. אני ממלית לקרוא מדריך בסיסי על אינדקסים. אתה צריך להבין שאינדקס הוא בסך הכל מיפוי של נתונים, בדיוק כמו שאינדקס בספר לימוד הוא מיפוי של התכנים של הספר לפי דפים. האם האינדקס משתנה בספר כשאתה קורא בספר? מתי יהיה צורך בשינוי האינדקס בספר לימוד?

    כמו שכתבתי בהצלחה לא ברור לי מה הכוונה שלך בכלל בשאלה. היא נשמעתץ קצת חסרת הגיון. אם אני אנחש אז השאלה שלך היא אולי: מתי השרת משתמש באינדקס בשאילתת SELECT

    אם זו השאלה אז התשובה פשוטה...
    תבדוק לבד :-)

    הסבר: שרתי SQL כוללים מנוע מיטוב או מנוע הרצה. בניגוד לחלק מהשרתים האחרים, שרת SQL לא מריץ את השאילתה AS IT IS. לאחר שאתה מורה לשרת להריץ שאילתה, אז מתחיל תהליך מורכב הכולל בין השאר של ניתוח השאילתה. השאילתה מפורקת לגורמים השונים שלה, מנוע המיטוב בונה מספר תוכניות הרצה שונות, ובסופו של דבר בוחר תוכנית הרצה ש"נראית" לו מספיק טובה. עד כאן בכמה מילים כלליות... ז"א ייתכן שאותה שאילתה בשתי מסדי נתונים שונים תרוץ באופן שונה לחלוטין! חשוב מאוד לזכור את זה, וזה גם הסיבה שבפורום תמיד צריך לצרף DDL+DML (תבדוק מה זה כאן)

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

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

    כיצד נסתכל בתוכנית ההרצה בתוכנת ה SSMS?

    כתוב שאילתה ב SSMS וחפש כפתור הצג תוכנית הרצה מצופה. השרת יציג לך את את התוכנית ההרצה שהוא מתכנן להריץ.

    * לעיתים בזמן ההרצה בפועל הדברים מעט משתנים ולכן אפשר גם לצפות בתוכנית ההרצה מייד אחרי ההרצה. לשם כך לחץ על הכפתור הצג תונכית הרצה ממשית.

    ראה בתמונה הבאה את 2 הכפתורים:

    אני מקווה שזה עונה על השאלה שלך :-)


    [Personal Site] [Blog] [Facebook]signature

    • הוצע כתשובה על-ידי Eran Sharvit יום שני 17 מרץ 2014 09:59
    • סומן כתשובה על-ידי Eran Sharvit יום ראשון 23 מרץ 2014 14:06
    יום שישי 14 מרץ 2014 17:08
    מנחה דיון
  • בוקר טוב,

    אנא לצטט במדוייק :-)
    לא כתבתי בצורה גורפת שאי אפשר לבצע אינדקס VIEW על טור וירטואלי באופן גורף אלא רשמתי "לא יכול ליצור עליו אינדקס בצורה זו".

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

    1. אני לא יודע אם זה יעבוד לך אבל הרבה פעמים אפשר לעקוף מגבלות כאלה. למשל (*) אם באינדקס VIEW אסור לבצע שאילתה פנימית, אז אפשר לנסות להגיע לאותה תוצאה בלי שאילתה פנימית, אלא עם JOIN למשל, (*) אם אסור LEFT JOIN אז אפשר לבצע LEFT JOIN באמצעות CROSS JOIN (שכן מותר באינדקס על VIEW למשל) ועוד... בקיצור הרבה פעמים אפשר להגיע לתוצאת השאילתה בלי שמוש בדרך המקובלת. כאן נכנס הקישור עליו אמרתי שאני לא יודע אם יעזור אבל אולי תוכל לנסות להגיע לאותה תוצאה של שאילתה בדרך שונה...

    http://social.technet.microsoft.com/wiki/contents/articles/20583.crazy-tsql-queries-play-time.aspx

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

    3. אני אעבור על השאלה שלך בעוד כרגע... בתקווה שתיהיה לי תשובה או כיוון...


    [Personal Site] [Blog] [Facebook]signature

    • נערך על-ידי pituachMVP, Editor יום חמישי 20 מרץ 2014 10:52
    • סומן כתשובה על-ידי Matanya Zac יום שלישי 25 מרץ 2014 21:45
    יום חמישי 20 מרץ 2014 10:25
    מנחה דיון
  • הי,

    קצת ארוך הדיון הזה, ויש פה יותר מדי תשובות שלא עונות על השאלה...

    אני אנסה לענות בצורה תמציתית וברורה, ואני מקווה שאצליח לענות על השאלה המקורית...

    קודם כל, יש המון מגבלות על יצירת Indexed View. אפשר לראות כאן רשימה מפורטת. אחת המגבלות היא שאי אפשר להשתמש ב-Subqueries בתוך ה-View.

    אם הצלחת לעמוד בכל המגבלות האלה בהצלחה וליצור Clustered Index על ה-View, אז כל העמודות שחוזרות מה-View נשמרות באינדקס. זה כולל עמודות מחושבות (כמו v1 בדוגמא שלך). התוצאה של העמודה v1 נשמרת כעמודה רגילה ב-Clustered Index.

    לבסוף, לשאלתך כיצד SQL Server יודע לעדכן את האינדקס כאשר מתבצעים שינויים על טבלאות שה-View פונה אליהן... קודם כל, הוא רושם בצד את כל התלויות בין ה-Indexed View לבין הטבלאות והעמודות שהוא תלוי בהן. בכל פעם שיש שינוי בעמודה כזאת, הוא מזהה את הרשומות ב-Indexed View שמושפעות מהשינוי (לכן האינדקס חייב להיות Unique), והוא מחשב מחדש את ערכי העמודות באינדקס באותן רשומות. החישוב הזה יכול להיות מסובך, וזאת בדיוק הסיבה שיש כל-כך הרבה מגבלות על יצירה של Indexed View. אם אתה עומד בכל המגבלות האלה, אז ה-View שלך מספיק פשוט, כך ש-SQL Server יוכל לבצע את החישוב הזה...

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Eran Sharvit יום ראשון 23 מרץ 2014 14:07
    יום ראשון 23 מרץ 2014 08:59
    מנחה דיון
  • אחד מאתרי הבלוגים הטובים יותר לדעתי הוא של brent ozar

    תבדוק אם המאמר הבא עוזר לך:
    http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

    הנה עוד קישור שניראה טוב במבט ראשון:

    Understanding When Statistics Will Automatically Update
    http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/


    [Personal Site] [Blog] [Facebook]signature

    • סומן כתשובה על-ידי Matanya Zac יום שני 07 אפריל 2014 14:40
    שבת 29 מרץ 2014 12:04
    מנחה דיון
  • הי,

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

    זאת אחת הסיבות המרכזיות לכך ש-Indexed View עלול להשפיע לרעה על ביצועי המערכת. אם יש במערכת הרבה עדכונים, שגוררים עדכון של הרבה אינדקסים, זאת יכולה להיות צרה צרורה...

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Matanya Zac יום שני 07 אפריל 2014 14:40
    יום שני 31 מרץ 2014 06:16
    מנחה דיון

כל התגובות

  • שבת שלום,

    השאלה מנוסחת בצורה שלא כל כל ברורה לי. אני אנסה לענות על מה שאני מבין\

    בעת יצירת אינדקס על view , כל גוף התוצאה נשמר באינדקס עצמו

    הרעיון של אינדקס הוא ליצור מיפוי של הנתונים. לפעמים גם הנתונים עצמם נשמרים ולפעמים רק הפנייה pointer בהתאם לסוג האינדקס למשל.

    כיצד ה SQL יודע להבחין בשינויים הנעשים בשורות הטבלאות המצריכות לחשב מחדש שורה בשאילתא.

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

    למשל, נניח שהשאילתא מכילה התניית case , אשר פונה לטבלת עזר, ולפי זה הערך מחושב.

    אין שום קשר בין שאילתת SELECT ובין בימצוע חישוב מחדש. אינדקס לא משתנה בזמן שאילתת בחירה אלא בזמן שאילתות שמשנות את הנתונים הקשורים לאינדקס. אני ממלית לקרוא מדריך בסיסי על אינדקסים. אתה צריך להבין שאינדקס הוא בסך הכל מיפוי של נתונים, בדיוק כמו שאינדקס בספר לימוד הוא מיפוי של התכנים של הספר לפי דפים. האם האינדקס משתנה בספר כשאתה קורא בספר? מתי יהיה צורך בשינוי האינדקס בספר לימוד?

    כמו שכתבתי בהצלחה לא ברור לי מה הכוונה שלך בכלל בשאלה. היא נשמעתץ קצת חסרת הגיון. אם אני אנחש אז השאלה שלך היא אולי: מתי השרת משתמש באינדקס בשאילתת SELECT

    אם זו השאלה אז התשובה פשוטה...
    תבדוק לבד :-)

    הסבר: שרתי SQL כוללים מנוע מיטוב או מנוע הרצה. בניגוד לחלק מהשרתים האחרים, שרת SQL לא מריץ את השאילתה AS IT IS. לאחר שאתה מורה לשרת להריץ שאילתה, אז מתחיל תהליך מורכב הכולל בין השאר של ניתוח השאילתה. השאילתה מפורקת לגורמים השונים שלה, מנוע המיטוב בונה מספר תוכניות הרצה שונות, ובסופו של דבר בוחר תוכנית הרצה ש"נראית" לו מספיק טובה. עד כאן בכמה מילים כלליות... ז"א ייתכן שאותה שאילתה בשתי מסדי נתונים שונים תרוץ באופן שונה לחלוטין! חשוב מאוד לזכור את זה, וזה גם הסיבה שבפורום תמיד צריך לצרף DDL+DML (תבדוק מה זה כאן)

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

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

    כיצד נסתכל בתוכנית ההרצה בתוכנת ה SSMS?

    כתוב שאילתה ב SSMS וחפש כפתור הצג תוכנית הרצה מצופה. השרת יציג לך את את התוכנית ההרצה שהוא מתכנן להריץ.

    * לעיתים בזמן ההרצה בפועל הדברים מעט משתנים ולכן אפשר גם לצפות בתוכנית ההרצה מייד אחרי ההרצה. לשם כך לחץ על הכפתור הצג תונכית הרצה ממשית.

    ראה בתמונה הבאה את 2 הכפתורים:

    אני מקווה שזה עונה על השאלה שלך :-)


    [Personal Site] [Blog] [Facebook]signature

    • הוצע כתשובה על-ידי Eran Sharvit יום שני 17 מרץ 2014 09:59
    • סומן כתשובה על-ידי Eran Sharvit יום ראשון 23 מרץ 2014 14:06
    יום שישי 14 מרץ 2014 17:08
    מנחה דיון
  • תודה על התשובה, אני מודה על בורותי, יכול להיות שיש איזה דבר בסיסי שאני לא מבין. אבל לא הביישן למד. שאלתי הייתה ממוקדת על אינדוקס view , כאשר לפי מיטב ידיעתי, המערכת שומרת את נתוני התוצאה עצמה של כל השדות על האינדקס עצמו. השאילתא מוגבלת במספר דברים, אך לא מצאתי בין הדברים הגבלה על case . כנראה שה case שכתבתי לא קביל בגלל שיש לו שאילתא פנימית, אני לא בטוח. תודה.
    יום שני 17 מרץ 2014 08:52
  • בוקר טוב

    כל מה שכתבתי נכון גם לגבי אינדקס על VIEW, אין הבדל ברעיון הבסיסי של שימוש באינדקס.

    * כדאי לציין שאם יש אינדקס על VIEW אז אפילו אם פונים בשאילתה על הטבלה עצמה ולא ל VIEW אז השרת לפעמים יעשה שמוש באינדקס של ה VIEW.

    * לגבי מה קביל או לא קביל ובכל דיון על שאילתה מסויימת, אם תצרף לנו DDL+DML אז נוכל להמשיך את הדיון (אין מניעה עקרונית בשימוש בשאילתות פנימיות בשאילתה).

    עוד ניתן לקרוא על אינדקס על VIEW בקישור הבא:
    http://msdn.microsoft.com/en-us/library/ms191432.aspx


    [Personal Site] [Blog] [Facebook]signature

    יום שני 17 מרץ 2014 11:14
    מנחה דיון
  • CREATE TABLE dbo.tbl1
    	(
    	id int NOT NULL,
    	code int NULL
    	)  ON [PRIMARY]
    GO
    ALTER TABLE dbo.tbl1 ADD CONSTRAINT
    	PK_tbl1 PRIMARY KEY CLUSTERED 
    	(
    	id
    	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    GO
    
    
    CREATE TABLE dbo.tbl2
    	(
    	id int NOT NULL,
    	code int NULL
    	)  ON [PRIMARY]
    GO
    ALTER TABLE dbo.tbl2 ADD CONSTRAINT
    	PK_tbl2 PRIMARY KEY CLUSTERED 
    	(
    	id
    	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    GO
    
    
    select t1.id,t1.code,(
      case when exists(select * from tbl2 where code=t1.code) then 1 else 2  end) v1
    from tbl1 t1

    יום רביעי 19 מרץ 2014 00:13
  • לילה טוב,

    לא הצלחתי כל כך להבין מה הבעיה. הנה כל הקוד כולל יצירת ה VIEW ואני לא רואה שום סיבה להודעות שגיאה בהרצה. נסה להריץ את הקוד קטע אחרי קטע, או אולי להבהיר את הבעיה שוב (עתה שיש לנו DDL+DML אולי יהה יותר לשחזר ולהבין מה הבעיה)

    /******************************************************* DDL */
    CREATE TABLE dbo.tbl1
    	(
    	id int NOT NULL,
    	code int NULL
    	)  ON [PRIMARY]
    GO
    ALTER TABLE dbo.tbl1 ADD CONSTRAINT
    	PK_tbl1 PRIMARY KEY CLUSTERED 
    	(
    	id
    	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    GO
    
    CREATE TABLE dbo.tbl2
    	(
    	id int NOT NULL,
    	code int NULL
    	)  ON [PRIMARY]
    GO
    ALTER TABLE dbo.tbl2 ADD CONSTRAINT
    	PK_tbl2 PRIMARY KEY CLUSTERED 
    	(
    	id
    	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    
    GO
    
    /******************************************************* DML */
    -- את החלק הזה אתה היית אמור להעביר אלינו!
    -- זה החלק של ה 
    -- DML
    -- הכנסת כמה ערכים לדוגמה כדי שנוכל לבדוק insert tbl1 select 1,43523 UNION ALL select 2,324 UNION ALL select 3,643 UNION ALL select 4,23 UNION ALL select 5,34 insert tbl2 select 1,43523 UNION ALL select 2,11 UNION ALL select 3,12 UNION ALL select 4,23 UNION ALL select 5,34 /******************************************************* checking the data */ select * from tbl1 select * from tbl2 GO /******************************************************* simpole query */ select t1.id,t1.code,( case when exists(select * from tbl2 where code=t1.code) then 1 else 2 end) v1 from tbl1 t1 GO /******************************************************* create view using the same query */ ;create view V01 as select t1.id,t1.code,( case when exists(select * from tbl2 where code=t1.code) then 1 else 2 end) v1 from tbl1 t1 -- no problem or error /******************************************************* check the view */ select * from V01 GO /******************************************************* Clean all */ DROP VIEW V01 GO DROP TABLE tbl1 GO DROP TABLE tbl2 GO


    [Personal Site] [Blog] [Facebook]signature

    יום רביעי 19 מרץ 2014 02:22
    מנחה דיון
  • אני מתנצל, תשובתכם התעלמה שוב מהעיקר. אנחנו מדברים על אינדוקס ה view , ובכל הקוד שהוספתם לא היה מילה על זה. וכי מי חשב ש view כזה לא יעבוד? על כל פנים תודה רבה.

    נוסף על כך זו שאלה עקרונית כיצד המערכת עובדת. ולא שאלה שמתמקדת בשגיאה זו או אחרת. לכן לא ראיתי טעם להוסיף כאן את קוד האינדוקס:

    אני מניח שהוא יהיה כך:

    create view dbo.v01 WITH SCHEMABINDING  
    as
    select t1.id,t1.code,(
      case when exists(select * from tbl2 where code=t1.code) then 1 else 2  end) v1
    from tbl1 t1
    go
    
    CREATE UNIQUE CLUSTERED INDEX ixV01 ON dbo.v01(code)
    
    

    אין לי שום מטרה שתריצו את הקוד הזה כדי לראות אם הוא עובד או לא. השאלה העיקרית האם שדה v1 יירשם באינדקס, וכיצד המערכת תדע ששינוי בשורה בטבלה tbl2 מחייב חישוב מחדש של האינדקס חלקו/או כולו.

    בכל אופן אני מבין שיש משהוא לא תקין בשאלה שלי, כי עובדה שזה 3 פעמים התשובה מדברת על עניין אחר. שוב תודה

    יום רביעי 19 מרץ 2014 21:07
  • אוקיי, אמרתי לך שאני לא מבין מה בדיוק השאלה :-)

    * לא בטוח שהבעיה בשאלה, אולי בצד שלי דווקא (בהבנה), אבל העיקר להגיע למישור השווה בסוף :-)

    * בכל תגובה שלך אני מבין משהו אחר לחלוטין שאתה מחפש/שואל :-)
    יש סיבה שפורום אינו המקום האידאלי להסבר ולהציג אפיון או צרכים.

    בכל מקרה...

    אני אתן כמה נקודות ושוב אני דיי בטוח שזו לא השאלה, מכיוון שאני למשל לא מבין את המשפט "שדה v1 יירשם באינדקס". טור v1 בשאילתה הוא רק שם וירטואלי של תוצאת השאילתה הפנימית והחלק של ה CASE. זה חלק מחושב שקיבל כינוי v1 בצורה דינאמית. האינדקס בקוד שלך לא נעשה על חלק זה. יכול להיות שהתכוונת לאינדקס הבא?

    CREATE UNIQUE CLUSTERED INDEX ixV01 ON dbo.v01(v1)
    GO

    ניסיון ליצור אינדקס על v1 יעלה שגיאה שמסבירה את הבעיה ובקצור אתה לא יכול ליצור עליו אינדקס בצורה זו:
    Column 'v1' in view 'dbo.v01' cannot be used in an index or statistics or as a partition key because it does user or system data access

    לפני הכל! כשרשמתי את הקוד אז זה היה במנותק מההבהרה כרגע, שאתה רוצה ליצור אינדקס על ה VIEW. אם רוצים ליצור על ה VIEW אינדקס צריך לבצע SCHEMABINDING ל view. ז"א במקרה הנוכחי של הקוד שכתבתי היה צריך לבצע תיקון:

    ;ALTER view V01 WITH SCHEMABINDING as
    select t1.id,t1.code,(
      case when exists(select id,code from dbo.tbl2 where code=t1.code) then 1 else 2  end) v1
    from dbo.tbl1 t1

    ז"א (1) להוסיף את הרמז WITH SCHEMABINDING להוראה, (2) אסור לעבוד עם כוכביות בשאילתה אלא חייבים לעבוד עם שמות הטורים, (3) כל השמות צריכים להיות בשיטת 2 החלקים, ז"א סכמה+שם טבלה. אבל במקרה הנוכחי זה לא יספיק...

    יש כמה מגבלות על יצירת אינדקס על VIEW (בניגוד למגבלות על יצירת ה VIEW שקודם כתבתי). אסור למשל שימוש ב subqueries, unions, uter joins וכו'. יכול להיות שזה מה שהתכוונת קודם (מגבלות על VIEW בשביל ליצור אינדקס ולא כפי שאני הבנתי מגבלות על VIEW). אם זה המצב אז כן... מגבלות אלו קיימות.


    [Personal Site] [Blog] [Facebook]signature

    • נערך על-ידי pituachMVP, Editor יום רביעי 19 מרץ 2014 23:46
    • סומן כתשובה על-ידי Matanya Zac יום חמישי 20 מרץ 2014 06:35
    • סימון כתשובה בוטל על-ידי Matanya Zac יום חמישי 20 מרץ 2014 06:51
    יום רביעי 19 מרץ 2014 23:44
    מנחה דיון
  • לפי טענתכם א"א אפשר לבצע אינדקס על שדה ווירטואלי, וכנראה שהוא גם לא שומר זאת באינדקס. אך למעשה בדקתי את העניין, הדוגמה שנתתי נפלה מסיבת שימוש בתת-שאילתא רק ב join . אבל הוא כן מרשה שדה ווירטואלי. הנה, קוד זה לא נותן שגיאה:

    create view dbo.v01 WITH SCHEMABINDING  
    as
    select t1.id,t1.code,
      (case when (code % 2)=0 then 1 else 4 end) v1
      --exists(select code from dbo.tbl2 where code=t1.code) then 1 else 2  end) v1
    from dbo.tbl1 t1
    go
    
    
    
    CREATE UNIQUE CLUSTERED INDEX ixV01 ON dbo.v01(id,code,v1)
    
    

    זה נותן למעשה מפתח כיצד הוא מגלה את השינוי שדורש חישוב מחדש של האינדקס ואיכסון מחודש של שדות התוצאה. זאת אומרת שהוא מחשב ברוורס את התוצאה לפי שורות ה join המתקבלות. מה דעתכם?
    יום חמישי 20 מרץ 2014 06:57
  • בוקר טוב,

    אנא לצטט במדוייק :-)
    לא כתבתי בצורה גורפת שאי אפשר לבצע אינדקס VIEW על טור וירטואלי באופן גורף אלא רשמתי "לא יכול ליצור עליו אינדקס בצורה זו".

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

    1. אני לא יודע אם זה יעבוד לך אבל הרבה פעמים אפשר לעקוף מגבלות כאלה. למשל (*) אם באינדקס VIEW אסור לבצע שאילתה פנימית, אז אפשר לנסות להגיע לאותה תוצאה בלי שאילתה פנימית, אלא עם JOIN למשל, (*) אם אסור LEFT JOIN אז אפשר לבצע LEFT JOIN באמצעות CROSS JOIN (שכן מותר באינדקס על VIEW למשל) ועוד... בקיצור הרבה פעמים אפשר להגיע לתוצאת השאילתה בלי שמוש בדרך המקובלת. כאן נכנס הקישור עליו אמרתי שאני לא יודע אם יעזור אבל אולי תוכל לנסות להגיע לאותה תוצאה של שאילתה בדרך שונה...

    http://social.technet.microsoft.com/wiki/contents/articles/20583.crazy-tsql-queries-play-time.aspx

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

    3. אני אעבור על השאלה שלך בעוד כרגע... בתקווה שתיהיה לי תשובה או כיוון...


    [Personal Site] [Blog] [Facebook]signature

    • נערך על-ידי pituachMVP, Editor יום חמישי 20 מרץ 2014 10:52
    • סומן כתשובה על-ידי Matanya Zac יום שלישי 25 מרץ 2014 21:45
    יום חמישי 20 מרץ 2014 10:25
    מנחה דיון
  • לצערי אין לנו מידע על מאחורי הקלעים פרט למה שניתן לקורא במאמרים של צוות הפיתוח של ה SQL ואני לא זוכר שנתקלתי בהסבר מקיף על סדר הפעולות שגורר עדכון אינדקס. לכן כל מה שאני כותב כאן כרגע הוא רק הערכה שלי כמי שגם עוסק בפיתוח וכמובן מההכרה של ה SQL!

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

    דוגמה תיאורטית של תהליך האירועים שלב אחרי שלב:

    1. המשתמש שולח הוראה לבניית אינדקס (לשם הדוגמה אני מוציא בכוונה את החלק הישיר של שימוש בטור code באינדקס ומשאיר אינדוקס רק על id,v1)

    create view dbo.v01 WITH SCHEMABINDING  
    as
    select t1.id,t1.code,
      (case when (code % 2)=0 then 1 else 4 end) v1end) v1
    from dbo.tbl1 t1
    go
    CREATE UNIQUE CLUSTERED INDEX ixV01 ON dbo.v01(id,v1)
    go

    2. (ניחוש שלי): השרת בודק את האינדקס ו"רואה" שהוא תלוי בטורים id,v1 ז"א בשלב יצירת האינדקס לראשונה הוא חקר אותו לעומק כולל החלק של ה טור הדינאמי.

    3. השרת כותבת ב header של האינדקס את רשימת הטורים הקשורים לאינדקס id,v1

    4. המשתמש מבצע שינוי כלשהו ב DML (בדטא)

    5. השרת בודק רק את ה HEADER בצורה יעילה ומוצא שאין סיבה או יש סיבה לעדכן את האינדקס.

    אני מקווה שזה עונה נכונה על השאלה :-)


    [Personal Site] [Blog] [Facebook]signature

    יום חמישי 20 מרץ 2014 10:43
    מנחה דיון
  • הי,

    קצת ארוך הדיון הזה, ויש פה יותר מדי תשובות שלא עונות על השאלה...

    אני אנסה לענות בצורה תמציתית וברורה, ואני מקווה שאצליח לענות על השאלה המקורית...

    קודם כל, יש המון מגבלות על יצירת Indexed View. אפשר לראות כאן רשימה מפורטת. אחת המגבלות היא שאי אפשר להשתמש ב-Subqueries בתוך ה-View.

    אם הצלחת לעמוד בכל המגבלות האלה בהצלחה וליצור Clustered Index על ה-View, אז כל העמודות שחוזרות מה-View נשמרות באינדקס. זה כולל עמודות מחושבות (כמו v1 בדוגמא שלך). התוצאה של העמודה v1 נשמרת כעמודה רגילה ב-Clustered Index.

    לבסוף, לשאלתך כיצד SQL Server יודע לעדכן את האינדקס כאשר מתבצעים שינויים על טבלאות שה-View פונה אליהן... קודם כל, הוא רושם בצד את כל התלויות בין ה-Indexed View לבין הטבלאות והעמודות שהוא תלוי בהן. בכל פעם שיש שינוי בעמודה כזאת, הוא מזהה את הרשומות ב-Indexed View שמושפעות מהשינוי (לכן האינדקס חייב להיות Unique), והוא מחשב מחדש את ערכי העמודות באינדקס באותן רשומות. החישוב הזה יכול להיות מסובך, וזאת בדיוק הסיבה שיש כל-כך הרבה מגבלות על יצירה של Indexed View. אם אתה עומד בכל המגבלות האלה, אז ה-View שלך מספיק פשוט, כך ש-SQL Server יוכל לבצע את החישוב הזה...

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Eran Sharvit יום ראשון 23 מרץ 2014 14:07
    יום ראשון 23 מרץ 2014 08:59
    מנחה דיון
  • תודה רבה התשובות עזרו לי מאוד.

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

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

    תודה

    יום שלישי 25 מרץ 2014 21:51
  • אחד מאתרי הבלוגים הטובים יותר לדעתי הוא של brent ozar

    תבדוק אם המאמר הבא עוזר לך:
    http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

    הנה עוד קישור שניראה טוב במבט ראשון:

    Understanding When Statistics Will Automatically Update
    http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/


    [Personal Site] [Blog] [Facebook]signature

    • סומן כתשובה על-ידי Matanya Zac יום שני 07 אפריל 2014 14:40
    שבת 29 מרץ 2014 12:04
    מנחה דיון
  • הי,

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

    זאת אחת הסיבות המרכזיות לכך ש-Indexed View עלול להשפיע לרעה על ביצועי המערכת. אם יש במערכת הרבה עדכונים, שגוררים עדכון של הרבה אינדקסים, זאת יכולה להיות צרה צרורה...

    בהצלחה!

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Matanya Zac יום שני 07 אפריל 2014 14:40
    יום שני 31 מרץ 2014 06:16
    מנחה דיון