בעקבות Highload++ Siberia 2019 - 8 משימות ב-Oracle

היי!

בתאריכים 24-25 ביוני התקיים כנס Highload++ Siberia 2019 בנובוסיבירסק. גם החבר'ה שלנו היו שם להגיש תלונה "מאגרי מידע של אורקל (CDB/PDB) והשימוש המעשי שלהם לפיתוח תוכנה", נפרסם גרסת טקסט מעט מאוחר יותר. היה מגניב, תודה אולגבונין למען הארגון, כמו גם לכל מי שהגיע.

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

מהו ערך הרצף המקסימלי שנראה כתוצאה מביצוע הסקריפט הבא?

create sequence s start with 1;
 
select s.currval, s.nextval, s.currval, s.nextval, s.currval
from dual
connect by level <= 5;

• 1
• 5
• 10
• 25
• לא, תהיה שגיאה

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

כך, הערך המקסימלי יתאים למספר השורות, כלומר 5.

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

create table t(i integer check (i < 5));
 
create procedure p(p_from integer, p_to integer) as
begin
    for i in p_from .. p_to loop
        insert into t values (i);
    end loop;
end;
/
 
exec p(1, 3);
exec p(4, 6);
exec p(7, 9);

• 0
• 3
• 4
• 5
• 6
• 9

תשובהעל פי תיעוד של אורקל (מצוטט מ-11.2):

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

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

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

create table t(i integer, constraint i_ch check (i < 3));
 
begin
    insert into t values (1);
    insert into t values (null);
    insert into t values (2);
    insert into t values (null);
    insert into t values (3);
    insert into t values (null);
    insert into t values (4);
    insert into t values (null);
    insert into t values (5);
exception
    when others then
        dbms_output.put_line('Oops!');
end;
/

• 1
• 2
• 3
• 4
• 5
• 6
• 7

תשובהעל פי תיעוד של אורקל (מצוטט מ-11.2):

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

לפיכך, הערך null יעבור את הבדיקה, והחסימה האנונימית תתבצע בהצלחה עד לניסיון להכניס את הערך 3. לאחר מכן, חסימת הטיפול בשגיאות תנקה את החריג, לא תתרחש ביטול חזרה, ו יישארו ארבע שורות בטבלה עם ערכים 1, null, 2 ושוב null.

אילו זוגות ערכים יתפסו את אותה כמות מקום בבלוק?

create table t (
    a char(1 char),
    b char(10 char),
    c char(100 char),
    i number(4),
    j number(14),
    k number(24),
    x varchar2(1 char),
    y varchar2(10 char),
    z varchar2(100 char));
 
insert into t (a, b, i, j, x, y)
    values ('Y', 'Вася', 10, 10, 'Д', 'Вася');

• א' ו-X
• B ו-Y
• C ו-K
• C ו-Z
• K ו-Z
• אני וג'יי
• J ו-X
• הכל מופיע ברשימה

תשובהלהלן קטעים מהתיעוד (12.1.0.2) על אחסון נתונים מסוגים שונים באורקל.

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

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

NUMBER סוג נתונים
סוג הנתונים NUMBER מאחסן מספרים קבועים אפס וכן חיובי ושלילי עם ערכים מוחלטים מ-1.0 x 10-130 ועד אך לא כולל 1.0 x 10126. אם תציין ביטוי אריתמטי שלערך שלו יש ערך מוחלט הגדול או שווה ל- 1.0 x 10126, ואז אורקל מחזירה שגיאה. כל ערך NUMBER דורש בין 1 ל-22 בתים. אם לוקחים זאת בחשבון, ניתן לחשב את גודל העמודה בבתים עבור ערך נתונים מספרי מסוים NUMBER(p), כאשר p הוא הדיוק של ערך נתון, באמצעות הנוסחה הבאה: ROUND((length(p)+s)/2))+1 כאשר s שווה לאפס אם המספר חיובי, ו-s שווה 1 אם המספר שלילי.

בנוסף, בואו ניקח קטע מהתיעוד על אחסון ערכי Null.

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

על סמך הנתונים הללו, אנו בונים היגיון. אנו מניחים שמסד הנתונים משתמש בקידוד AL32UTF8. בקידוד זה, אותיות רוסיות יתפסו 2 בתים.

1) A ו-X, הערך של שדה a 'Y' לוקח 1 בייט, הערך של שדה x 'D' לוקח 2 בתים
2) B ו-Y, 'Vasya' ב-b הערך יהיה מרופד ברווחים של עד 10 תווים וייקח 14 בתים, 'Vasya' ב-d ייקח 8 בתים.
3) C ו-K לשני השדות יש את הערך NULL, אחריהם יש שדות משמעותיים, אז הם תופסים 1 בייט.
4) C ו-Z. לשני השדות יש את הערך NULL, אבל שדה Z הוא האחרון בטבלה, כך שהוא לא תופס מקום (0 בתים). שדה C תופס 1 בייט.
5) ק וז' בדומה למקרה הקודם. הערך בשדה K תופס 1 בייט, ב-Z – 0.
6) I ו-J. לפי התיעוד, שני הערכים ייקחו 2 בתים. אנו מחשבים את האורך באמצעות הנוסחה שנלקחה מהתיעוד: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J ו-X. הערך בשדה J ייקח 2 בתים, הערך בשדה X ייקח 2 בתים.

בסך הכל, האפשרויות הנכונות הן: C ו-K, I ו-J, J ו-X.

מה יהיה בערך גורם האשכולות של מדד T_I?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

• בערך עשרות
• בערך מאות
• בערך אלפים
• בערך עשרות אלפים

תשובהעל פי תיעוד של אורקל (מצוטט מ-12.1):

עבור אינדקס עץ B, גורם מקבץ האינדקס מודד את הקיבוץ הפיזי של שורות ביחס לערך אינדקס.

גורם מקבץ האינדקס עוזר למיטוב להחליט אם סריקת אינדקס או סריקת טבלה מלאה יעילה יותר עבור שאילתות מסוימות). גורם מקבץ נמוך מצביע על סריקת אינדקס יעילה.

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

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

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

create table t (
    a varchar2(N char),
    b varchar2(N char),
    c varchar2(N char),
    d varchar2(N char));
 
create index t_i on t (a, b, c, d);

• 100
• 200
• 400
• 800
• 1600
• 3200
• 6400

תשובהעל פי תיעוד של אורקל (מצוטט מ-11.2):

מגבלות מסד נתונים לוגי

פריט
סוג הגבלה
ערך מוגבל

אינדקסים
הגודל הכולל של העמודה שנוספה לאינדקס
75% מגודל בלוק מסד הנתונים פחות תקורה מסוימת

לפיכך, הגודל הכולל של העמודות שנוספו לאינדקס לא יעלה על 6Kb. מה שקורה לאחר מכן תלוי בקידוד הבסיס שנבחר. עבור קידוד AL32UTF8, תו אחד יכול לתפוס מקסימום 4 בתים, כך שבמקרה הגרוע ביותר, כ-6 תווים יתאימו ל-1500 קילובייט. לכן, אורקל תבטל יצירת אינדקס ב-N = 400 (כאשר אורך המפתח הגרוע ביותר הוא 1600 תווים * 4 בתים + אורך שורה), בעוד ב-N = 200 (או פחות) יצירת האינדקס תעבוד ללא בעיות.

האופרטור INSERT עם הרמז APPEND נועד לטעון נתונים במצב ישיר. מה קורה אם זה מוחל על השולחן שעליו תלוי ההדק?

• הנתונים ייטענו במצב ישיר, הטריגר יפעל כמצופה
• הנתונים ייטענו במצב ישיר, אך הטריגר לא יבוצע
• הנתונים ייטענו במצב קונבנציונלי, הטריגר יפעל כמו שצריך
• הנתונים ייטענו במצב רגיל, אך הטריגר לא יבוצע
• הנתונים לא ייטענו, תירשם שגיאה

תשובהבעיקרון, זו יותר שאלה של היגיון. כדי למצוא את התשובה הנכונה, הייתי מציע את מודל ההיגיון הבא:

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

אז התשובה הצפויה היא הנתונים ייטענו במצב רגיל (SQL), הדק יופעל.

על פי תיעוד של אורקל (מצוטט מ-8.04):

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

מה יקרה כשהסקריפט הבא יבוצע?

create table t(i integer not null primary key, j integer references t);
 
create trigger t_a_i after insert on t for each row
declare
    pragma autonomous_transaction;
begin
    insert into t values (:new.i + 1, :new.i);
    commit;
end;
/
 
insert into t values (1, null);

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

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

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

רק משתמשים רשומים יכולים להשתתף בסקר. להתחברבבקשה.

היה קשה?

• כמו שתי אצבעות, מיד החלטתי הכל נכון.

• לא ממש, טעיתי בכמה שאלות.

• פתרתי חצי נכון.

• ניחשתי את התשובה פעמיים!

• אני אכתוב בתגובות

14 משתמשים הצביעו. 10 משתמשים נמנעו.

מקור: www.habr.com