אינדקסים ב-Oracle12c

אחת מההרצאות שלי בilOUG Tech Days 2015 היתה “Indexes and Indexing in Oracle 12c”. דיברתי על פיצ'רים חדשים שנוספו בגרסה 12c שקשורים ל"אינדוקס" – לא בהכרח מילות מפתח חדשות בפקודת CREATE INDEX, אלא גם שינויים שיכולים להשפיע על הדרך בה אנחנו בוחרים להשתמש (או לא להשתמש) באינדקסים.

הנה סיכום של הפיצ'רים עליהם דיברתי, או לפחות התכוונתי לדבר ואז נגמר לי הזמן :-(

שימו לב שחלק מהפיצ'רים האלה זמינים רק בגרסת ה-Enterprise Edition או ב-licensed option.

אם אתם רוצים לקרוא על הפיצ'רים האלה בפירוט, יש מקורות רבים וטובים. הטוב ביותר, לדעתי, הוא הבלוג של ריצ'רד פוט – ה"תנ"ך" של נושא האינדקסים באורקל – http://richardfoote.wordpress.com/category/12c/

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

לפני גרסה 12c לא ניתן להגדיר מספר אינדקסים על אותה רשימת עמודות.

בגרסה 12c זה אפשרי, בתנאי ש:

– רק אחד האינדקסים visible בכל רגע נתון (היכולת להפוך אינדקס ל-invisible נוספה בגרסה 11g)
– האינדקסים שונים זה מזה בתכונה כלשהי (unique/non-unique, reverse/regular, partitioned/non-partitioned, וכו')

למה זה טוב?

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

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

אם צריך את הפונקציונליות הזאת לפני גרסה 12c אפשר להשתמש בטריק ישן וליצור את האינדקס החדש על כמעט אותה רשימת עמודות – ע"י הוספת עמודה "מלאכותית" – למשל, אם יש לנו אינדקס על T (COL1, COL2) נוכל ליצור אינדקס אחר על T (COL1, COL2, NULL).

פעולות Online

בגרסה 12c יותר פעולות DDL ניתנות לביצוע online (מה שאומר, בצורה גסה, פחות נעילות חוסמות בסביבה מרובת משתמשים). בהתמקדות על אינדקסים, אלה השינויים הרלבנטיים:

– הפעולות הבאות מתבצעות offline לפני גרסה 12c ו-online ב-12c:

ALTER INDEX VISIBLE
ALTER INDEX INVISIBLE

– הפעולות הבאות מתבצעות offline כברירת מחדל, אבל ב-12c הן יכולות להתבצע online ע"י הוספת מילת המפתח ONLINE:

DROP INDEX [ONLINE]
ALTER INDEX UNUSABLE [ONLINE]
ALTER TABLE MOVE SUB/PARTITION [ONLINE]

תחזוקה א-סינכרונית של אינדקסים גלובליים

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

לפני גרסה 12c, ביצוע DROP או TRUNCATE של partition של טבלה זו הוא או מהיר (והופך את האינדקסים הגלובליים להיות unusable) או שומר את האינדקסים הגלובליים usable (אבל אורך יותר זמן).

בגרסה 12c, הפעולה יכולה להיות גם מהירה וגם שומרת על האינדקסים הגלובליים usable. כאשר מציינים UPDATE GLOBAL INDEXES, ה-partition נזרק או מתנקה והאינדקסים נשארים usable למרות שהכניסות באינדקס לא נמחקות.

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

הכניסות היתומות מתנקות בצורה א-סינכרונית, כלומר לא כחלק מפקודת ה-DROP או ה-TRUNCATE. זה קורה ע"י ג'וב תחזוקה, ע"י ALTER INDEX REBUILD, ע"י ALTER INDEX COALESCE CLEANUP, או ע"י קריאה במפורש ל-DBMS_PART.CLEANUP_GIDX).

אינדקסים חלקיים

בגרסה 12c ניתן ליצור אינדקס (לוקאלי או גלובלי) על חלק מה-partition-ים (כל עוד הוא לא unique ואינו משמש לאכיפת מפתח ראשי או מפתח יחודי).

כל partition של טבלה מוגדר כ-INDEXING ON או INDEXING OFF (במפורש ברמת ה-partition או ע"י ירושה של ההגדרה מרמת הטבלה).

כעת, אם האינדקס נוצר עם הפסוקית החדשה INDEXING PARTIAL, אז רק רשומות מ-partitionים עם “INDEXING ON” נכללות באינדקס.

זה יכול להיות שימושי, למשל, כדי לאנדקס רק partition-ים מהתקופה האחרונה, כאשר אין כמעט גישה ל-partition-ים ישנים יותר. דוגמה נוספת: טעינה של נתונים לתוך partition חדש אשר מוגדר עם “INDEXING OFF” כאשר partition-ים ישנים יותר מוגדרים עם “INDEXING ON” (כדי להפוך את הטעינה למהירה יותר תוך שמירה על גישות יעילות לנתונים הישנים יותר), וכאשר הטעינה מסתיימת הפיכה של ה-partition החדש ל-“INDEXING ON”.

דחיסת אינדקסים מתקדמת

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

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

המצגת ניתנת להורדה מעמוד המצגות.

Split

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

create or replace function split (i_str in varchar2, i_delimiter in varchar2 default ',') 
  return sys.odcivarchar2list
  pipelined
  as
    l_current_string varchar2(4000) := i_str;
    l_pos            binary_integer;
  begin
    if i_str is null then
        return;
    end if;
    loop
      l_pos := nullif(instr(l_current_string, i_delimiter), 0);
      pipe row(substr(l_current_string, 1, nvl(l_pos - 1, length(l_current_string))));
      exit when l_pos is null;
      l_current_string := substr(l_current_string, l_pos + length(i_delimiter));
  end loop;
end split;
/

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

select initcap(column_value) as name,
       length(column_value) as name_length
from   table(split('paris,london,rome,madrid'))
order  by column_value;

NAME                           NAME_LENGTH
------------------------------ -----------
London                                   6
Madrid                                   6
Paris                                    5
Rome                                     4

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

select * 
from table(split('Paris, France@@London, UK@@Rome, Italy@@Madrid, Spain',
                '@@'));

COLUMN_VALUE
--------------------
Paris, France
London, UK
Rome, Italy
Madrid, Spain

ilOUG Tech Days

השבוע השתתפתי ב “ilOUG Tech Days 2015” – כנס בן יומיים של ארגון משתמשי אורקל בישראל. הכנס התקיים במלון בחיפה, ואירח למעלה ממאה משתתפים ונבחרת מרשימה של מרצים מחו"ל ומהארץ.
לדעתי הארוע היה מצויין, מאורגן היטב ומבוצע כהלכה על ידי הנהלת ilOUG, ובפרט עמי אהרונוביץ' ולירון אמיצי (אתם יכולים לקרוא את הסיכום של לירון כאן).

אני העברתי שתי הרצאות והאזנתי להרבה הרצאות טובות של Bryn Llewellyn, Keith Laker, Heli Helskyaho, Jonathan Lewis, Tom Kyte, Joze Senegacnik ועמי אהרונוביץ'. הייתי רוצה לשמוע הרצאות נוספות, אבל לא יכולתי להיות בכמה מקומות באותו זמן…

ההרצאות שלי היו “Indexes and Indexing in Oracle 12c” ו “Deep Dive into Oracle 12c Pattern Matching”. בהרצאה השניה היה לי אורח מיוחד בקהל – Keith Laker מאורקל, מנהל המוצר של הפיצ'ר שהצגתי. זה היה מגניב שקית' היה שם, וגם מועיל, כי יכולתי להיעזר בו ולהפנות אליו שאלות, כמו:

  • האם MATCH_RECOGNIZE מוגדר ב- ANSI SQL? (תשובה: עדיין לא)
  • האם נדרש רשיון מיוחד לשימוש בו? (תשובה: לא, הפיצ'ר נתמך גם בגרסת ה-Standard Edition ללא עלות נוספת)
  • האם יש הבדלים בפיצ'ר בין גרסאות 12.1.0.1 ו- 12.1.0.2? (תשובה: לא)

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

עוד נקודות משמעותיות מבחינתי בכנס:

הפונקציות SET ו-CARDINALITY

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

שתי הפונקציות האלה הן SET ו-CARDINALITY, ושתיהן מקבלות כקלט פרמטר מסוג nested table.

SET מחזירה nested table מאותו טיפוס של פרמטר הקלט, לאחר שאיברים כפולים מוסרים ממנה.
CARDINALITY מחזירה את מספר האיברים בפרמטר הקלט.

שתי הפונקציות קיימות מגרסה 10.1.

ניצור nested table type:

create type string_ntt as table of varchar2(4000)
/

ונבנה אובייקט מהטיפוס הזה, תוך שימוש בקונסטרקטור, המכיל 6 איברים; 4 מתוכם יחודיים:

select string_ntt('A','B','C','B','D','A') as my_list from dual;

MY_LIST
------------------------------------------
STRING_NTT('A', 'B', 'C', 'B', 'D', 'A')

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

select CARDINALITY(string_ntt('A','B','C','B','D','A')) as num_of_elements from dual;

NUM_OF_ELEMENTS
---------------
              6

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

select SET(string_ntt('A','B','C','B','D','A')) as my_set from dual;

MY_SET
----------------------------------------
STRING_NTT('A', 'B', 'C', 'D')

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

select CARDINALITY(SET(string_ntt('A','B','C','B','D','A'))) as num_of_unique_elements from dual;

NUM_OF_UNIQUE_ELEMENTS
----------------------
                     4

ilOUG Day

ארגון משתמשי אורקל בישראל מציג שינוי מרענן בשיטת מפגשי הפורומים. ביום שני 23/3/2015 יתקיים לראשונה מפגש לכלל הקהילה הטכנולוגית, בניגוד למפגשים בעבר שהיו ממוקדים בכל פעם בקבוצת עניין מסויימת (ולכן גם קטנה במקרים רבים). במפגש הקרוב יתקיימו 15 הרצאות ב-5 מסלולים מקבילים:

  • Database Administration
  • Database Development
  • Engineered Systems, Hardware & OS
  • Development Technologies and Tools
  • Big Data & BI

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

אני אעביר במפגש את ההרצאה WRITE LESS code WITH MORE oracle 12c new features במסלול Database Development. הנה התקציר:

Oracle 12c introduced many new features that allow us developers to write less code than in previous releases, and become more efficient and productive.
Some features, such as Row Limiting and Lateral Inline Views, enhance the SQL language, so SQL statements can become much shorter and more readable than before.
Other features, such as Temporal Validity and In-Database Archiving, offer built-in functionality that previously had to be implemented by the application.
Attend this session to learn about several of these new features, and see many useful examples.

המפגש יתקיים במלון דן פנורמה בתל אביב, החל מהשעה 14:00. ההשתתפות היא ללא תשלום, אך יש להרשם מראש.
לרשימת ההרצאות המלאה, רישום ופרטים נוספים: http://www.iloug.org.il/ilOUGDay/?page=Agenda2

באג של האופטימייזר גורם לתוצאות שגויות

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

השאילתה מבוססת על הטבלה הבאה:

create table t(
  id   number not null constraint t_pk primary key,
  num1 number not null
);

נכניס לתוכה מספר רשומות:

insert into t values (1,74);
insert into t values (2,96);
insert into t values (3,41);

נתחיל עם השאילתה הבאה (שעובדת בסדר גמור):

select (select max(level) from dual connect by level <= t.num1) 
from   t
where  t.id = :id;

עם הנתונים של הדוגמה נצפה שהשאילתה תחזיר עבור id=1 את הערך 74, עבור id=2 את הערך 96, ועבור id=3 את הערך 41. ואכן אלה התוצאות שחוזרות.
הנה ה-execution plan:

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID  | T    |     1 |    26 |     1   (0)| 00:00:01 |
|*  5 |   INDEX UNIQUE SCAN           | T_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=:B1)
   5 - access("T"."ID"=TO_NUMBER(:ID))

כעת, נוסיף בסך הכל inline view תמים לשאילתה:

select (select max(level) from dual connect by level <= t.num1) 
from   t,
       (select distinct 1 from dual)
where  t.id = :id;

מן הסתם, הוספת ה-inline view - select distinct 1 from dual - לא אמורה לשנות דבר. הוא מחזיר רשומה אחת, אנחנו לא משתמשים במה שהוא מחזיר, ואין תנאי join בשאילתה (כך שלמעשה אנחנו עושים מכפלה קרטזית בין רשומה בודדת ל-[אפס או אחת] רשומות).

אבל עכשיו התוצאות שגויות. השאילתה מחזירה 1 בכל המקרים, והסיבה היא שתת-השאילתה הסקאלרית - (select max(level) from dual connect by level <= t.num1) - מבוצעת תמיד עם t.num1 = null.

נתבונן ב-execution plan של השאילתה הזאת:

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |    13 |     6  (17)| 00:00:01 |
|   1 |  SORT AGGREGATE               |           |     1 |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|           |       |       |            |          |
|   3 |    FAST DUAL                  |           |     1 |       |     2   (0)| 00:00:01 |
|   4 |  VIEW                         | VM_NWVW_0 |     1 |    13 |     4  (25)| 00:00:01 |
|   5 |   NESTED LOOPS                |           |     1 |    38 |     3   (0)| 00:00:01 |
|   6 |    FAST DUAL                  |           |     1 |       |     2   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN          | T_PK      |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=:B1)
   7 - access("T"."ID"=TO_NUMBER(:ID))

משום מה, האופטימייזר החליט שאין צורך לגשת לטבלה T. אורקל ניגש רק לאינדקס T_PK, שמכיל רק את העמודה ID. עם ה-execution plan הזה בלתי אפשרי להגיע לערך של העמודה NUM1, ואכן התוצאות מראות ש-NUM1 מכיל לכאורה NULL.

צעד מספר 4 ב-execution plan מתייחס ל-VM_NWVW_0, שבדרך כלל קשור ל-Complex View Merging. אז בואו ננסה לנטרל view merging בעזרת ההינט NO_MERGE:

select (select max(level) from dual connect by level <= t.num1) 
from   t,
       (select /*+ NO_MERGE */ distinct 1 from dual)
where  t.id = :id;

ואכן קיבלנו את התוצאות הנכונות, עם ה-execution plan הזה:

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |    26 |     6  (17)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |  NESTED LOOPS                 |      |     1 |    26 |     4  (25)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID | T    |     1 |    26 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN          | T_PK |     1 |       |     1   (0)| 00:00:01 |
|   7 |   VIEW                        |      |     1 |       |     3  (34)| 00:00:01 |
|   8 |    FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=:B1)
   6 - access("T"."ID"=TO_NUMBER(:ID))

בנוסף, אם נוריד את ה-DISTINCT מה-inline view, לא יתבצע complex view merging יותר, וה-execution plan (ולכן גם התוצאות) בסדר:

select (select max(level) from dual connect by level <= t.num1) 
from   t,
       (select 1 from dual)
where  t.id = :id;

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |    26 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |       |            |          |
|*  2 |   CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |  NESTED LOOPS                 |      |     1 |    26 |     3   (0)| 00:00:01 |
|   5 |   FAST DUAL                   |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T    |     1 |    26 |     1   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN          | T_PK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(LEVEL<=:B1)
   7 - access("T"."ID"=TO_NUMBER(:ID))

(אבל בשאילתה המקורית שלי ה-DISTINCT היה הכרחי...)

אתם מוזמנים להעיר הערות או לשאול שאלות בעמוד הזה, או לכתוב לי ל-oren@db-oriented.com.

DOAG 2014

אני אעביר שתי הרצאות בDOAG 2014 – הכנס השנתי של ארגון משתמשי אורקל בגרמניה.
הכנס יתקיים ב-18-20/11/2014 בנירנברג.
הנה תכנית הכנס המלאה.

ההרצאה הראשונה היא Edition Based Redefinition best practices. הנה התקציר:

Edition-Based Redefinition (EBR) is a powerful and fascinating feature of Oracle (added in version 11.2), that enables application upgrades with zero downtime, while the application is actively used and operational. Join this session to learn how to use EBR, see many live examples, and get tips from real-life experience in a production site using EBR extensively.

ההרצאה השניה היא Write Less (Code) with More (Oracle 12c New Features). הנה התקציר:

Oracle 12c introduced many new features that allow us developers to write less code than in previous releases, and become more efficient and productive.
Some features, such as Row Limiting and Lateral Inline Views, enhance the SQL language, so SQL statements can become much shorter and more readable than before.
Other features, such as Temporal Validity and In-Database Archiving, offer built-in functionality that previously had to be implemented by the application.
Attend this session to learn about several of these new features, and see many useful examples.

impdp – אילו רשומות נכשלו?

החל מגרסה 11.1, Data Pump Import תומך באופציה SKIP_CONSTRAINT_ERRORS, אשר מנחה את פעולת הטעינה להמשיך אפילו אם נגרמת הפרה של non-deferred constraints. התהליך רושם ללוג את כל הרשומות שהכנסתן נכשלת, אבל אינו עוצר, ומכניס את כל שאר הרשומות לטבלה. אם האופציה SKIP_CONSTRAINT_ERRORS אינה מוגדרת, אז התנהגות ברירת המחדל היא לבצע rollback לתהליך כולו.

לדוגמה:

הטבלה presidents נוצרה ע"י הפקודה הבאה:

create table presidents (
  id         number       primary key,
  first_name varchar2(20),
  last_name  varchar2(20)
);

והיא מכילה כרגע את הרשומות הבאות:

        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 George               Washington
         2 John                 Adams
         3 Thomas               Jefferson
         4 James                Madison
         5 James                Monroe
         6 John Quincy          Adams
         7 Andrew               Jackson
         8 Martin               Van Buren

יש לנו קובץ dump שמכיל export של הטבלה presidents (ממקום אחר או מזמן קודם), ואנו מעוניינים להוסיף את תוכן הקובץ לטבלה.
קובץ ה-dump מכיל את הנתונים הבאים:

        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         7 William              Harrison
         8 John                 Tyler
         9 James                Polk
        10 Zachary              Taylor
        11 Millard              Fillmore
        12 Franklin             Pierce

מובן שהרשומות בעלות הערכים 7 ו-8 בעמודת ID אינן יכולות להצטרף לטבלה, מאחר שאילוץ מפתח ראשי מוגדר על עמודת ID, והטבלה כבר מכילה רשומות עם ערכים אלה.

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

impdp oren/oren tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp1.log

Import: Release 11.2.0.4.0 - Production on Fri Jul 18 19:33:33 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
 Master table "OREN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
 Starting "OREN"."SYS_IMPORT_TABLE_01": oren/******** tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp1.log
 Processing object type TABLE_EXPORT/TABLE/TABLE
 ORA-39152: Table "OREN"."PRESIDENTS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 ORA-31693: Table data object "OREN"."PRESIDENTS" failed to load/unload and is being skipped due to error:
 ORA-00001: unique constraint (OREN.SYS_C0023857) violated
 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
 Job "OREN"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at 16:33:35

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

select * from presidents;

        ID FIRST_NAME           LAST_NAME
---------- -------------------- --------------------
         1 George               Washington
         2 John                 Adams
         3 Thomas               Jefferson
         4 James                Madison
         5 James                Monroe
         6 John Quincy          Adams
         7 Andrew               Jackson
         8 Martin               Van Buren

כעת נחזור על פעולת הטעינה עם SKIP_CONSTRAINT_ERRORS:

impdp oren/oren tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp2.log data_options=SKIP_CONSTRAINT_ERRORS

Import: Release 11.2.0.4.0 - Production on Fri Jul 18 19:38:00 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "OREN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OREN"."SYS_IMPORT_TABLE_01": oren/******** tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp2.log data_options=SKIP_CONSTRAINT_ERRORS
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "OREN"."PRESIDENTS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "OREN"."PRESIDENTS" 5.945 KB 4 out of 6 rows
2 row(s) were rejected with the following error:
ORA-00001: unique constraint (OREN.SYS_C0023857) violated

Rejected rows with the primary keys are:
Rejected row #1:
column ID: 7
Rejected row #2:
column ID: 8
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "OREN"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 16:38:06

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

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

ראשית הסתכלתי על הפקודות ש-Data Pump מבצע מאחורי הקלעים (אותן שלפתי מ-V$SQL), ואחת הפקודות שמצאתי היתה זו:

INSERT INTO "OREN"."ERR$DP004BC9640001" (
  ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_OPTYP$, ORA_ERR_TAG$,
  "ID", "FIRST_NAME", "LAST_NAME")
VALUES (:1, :2, :3, :4, :5, :6, :7, :8)

אם כך, נראה ש-Data Pump פשוט משתמש ב-DML Error Logging כדי לתמוך באופציה SKIP_CONSTRAINT_ERRORS. וזה אומר שבאופן עקרוני ערכי כל העמודות (למעט LOBs וכו') של הרשומות שנדחו זמינים. כל שעלינו לעשות הוא לשלוף מהטבלה ERR$DP004BC9640001, נכון?
ובכן, יש שתי בעיות עם זה:

  • שם הטבלה אינו קבוע. הרצתי את התהליך מספר פעמים ובכל פעם נוצרה טבלה עם שם אחר (אבל השם תמיד התחיל ב-ERR$DP).
  • הטבלה ERR$DP… נזרקת מיד עם סיום תהליך ה-import, כך שבשלב שבו אנחנו רוצים לשלוף ממנה היא כבר לא קיימת.

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

1. לפני שמתחילים את תהליך הטעינה, מתחברים עם SQL*Plus ומבצעים:

lock table presidents in share mode;

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

2. מתחילים את ה-import:

impdp oren/oren tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp.log data_options=SKIP_CONSTRAINT_ERRORS

Import: Release 11.2.0.4.0 - Production on Fri Jul 18 23:13:14 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Master table "OREN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "OREN"."SYS_IMPORT_TABLE_01": oren/******** tables=presidents dumpfile=presidents.dmp table_exists_action=append logfile=presidents_imp.log data_options=SKIP_CONSTRAINT_ERRORS
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "OREN"."PRESIDENTS" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

3. תהליך ה-import חסום כעת. נחזור ל-SQL*Plus ונמצא מה שם טבלת ה-error logging:

select table_name from user_tables where table_name like 'ERR$DP%';

TABLE_NAME
-----------------------
ERR$DP0047ABBF0001

4. נפתח חיבור שני עם SQL*Plus וננעל את טבלת ה-error logging בצורה שתאפשר לתהליך הטעינה להכניס לתוכה רשומות אבל לא לזרוק אותה:

lock table ERR$DP0047ABBF0001 in row share mode;

5. ב-SQL*Plus הראשון, נשחרר את הנעילה על טבלת presidents ונאפשר כך לתהליך ה-import להמשיך:

Rollback;

והתהליך ממשיך:

. . imported "OREN"."PRESIDENTS" 5.945 KB 4 out of 6 rows
2 row(s) were rejected with the following error:
ORA-00001: unique constraint (OREN.SYS_C0023889) violated

Rejected rows with the primary keys are:
Rejected row #1:
column ID: 7
Rejected row #2:
column ID: 8
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "OREN"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 20:35:21

6. לאחר שתהליך ה-import הסתיים, אנחנו יכולים לראות את כל הרשומות שנדחו במהלכו מאחד החיבורים שלנו ב-SQL*Plus:

select id,first_name,last_name from ERR$DP0047ABBF0001;

ID  FIRST_NAME           LAST_NAME
--- -------------------- --------------------
7   William              Harrison
8   John                 Tyler

7. ולסיום, למען הסדר הטוב, אנחנו יכולים לזרוק את טבלת ה-error logging מחיבור ה-SQL*Plus השני:

drop table ERR$DP0047ABBF0001;

אתם מוזמנים להעיר הערות או לשאול שאלות בעמוד הזה, או לכתוב לי ל-oren@db-oriented.com

3 נקודות על אינדקסים וסדר

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

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

CREATE INDEX T_IDX_1 ON T (COL_A,COL_B);
CREATE INDEX T_IDX_2 ON T (COL_B,COL_A);

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

SELECT *
FROM T
WHERE COL_A = :VAL1
  AND COL_B = :VAL2;

אבל לשאילתה הבאה האינדקס T_IDX_1 יתן מענה מצויין, ואילו T_IDX_2 לא:

SELECT *
FROM T
WHERE COL_A = :VAL1;

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

הערה: אם יש בטבלה כולה מספר קטן יחסית של ערכים שונים יחודיים בעמודה COL_B, אורקל יוכל עדיין לנצל אינדקס כ-T_IDX_2 על מנת לענות על השאילתה האחרונה תוך שימוש במסלול הגישה Index Skip Scan, אך עדיין שימוש באינדקס כ-T_IDX_1 עם Index Range Scan יהיה עדיף.

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

CREATE TABLE T (
   COL_A NUMBER,
   COL_B DATE,
   COL_C VARCHAR2(4000),
   …
);
CREATE INDEX T_IDX_A ON T (COL_A);
CREATE INDEX T_IDX_BA ON T (COL_B,COL_A);

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

כשיוצרים יותר מאינדקס אחד, האם יש חשיבות לסדר יצירת האינדקסים?
כאן התשובה חיובית במקרים מסויימים.
נניח שיצרנו את הטבלה T ומילאנו אותה במספר רב של רשומות, וכעת הגיע זמן יצירת האינדקסים. אפשר ליצור קודם את T_IDX_A ואחר כך את T_IDX_BA, או להיפך. נבחן את שתי האפשרויות:

מקרה א':

  • ניצור קודם את T_IDX_A. לצורך כך, אורקל יבצע Full Table Scan של הטבלה T (ויקח את הערך של העמודה COL_A מכל רשומה, וכמובן את ה-ROWID של כל רשומה כדי לדעת לאן להצביע מהאינדקס)
  • כעת ניצור את T_IDX_BA. שוב, אורקל יבצע Full Table Scan של הטבלה T (ויקח את הערך של העמודות COL_B ו- COL_A ואת ה-ROWID מכל רשומה)

מקרה ב':

  • ניצור קודם את T_IDX_BA. לצורך כך, אורקל יבצע Full Table Scan של הטבלה T (ויקח את הערך של העמודות COL_B ו- COL_A ואת ה-ROWID מכל רשומה)
  • כעת ניצור את T_IDX_A, וכאן נקודת המפנה בעלילה. אורקל יכול גם כאן לבצע Full Table Scan של T, אבל יש לו במקרה הזה חלופה נוספת, טובה יותר ברוב המקרים. כל מה שצריך על מנת לבנות את האינדקס זה הערכים של COL_A ושל ה-ROWID של כל הרשומות בטבלה (בהן COL_A הוא לא Null), והנתונים הללו קיימים כבר באינדקס T_IDX_BA. ולכן אורקל יכול לבצע Index Fast Full Scan של האינדקס T_IDX_BA, במקום Full Table Scan של הטבלה.

כלומר, אם כל העמודות של אינדקס אחד כלולות באינדקס שני, כדאי לבנות קודם את האינדקס השני ורק אחר כך את האינדקס הראשון, ולאפשר לאורקל יותר חלופות. ככל שבטבלה יש יותר עמודות שאינן כלולות באינדקסים, השיפור בזמן יצירת האינדקס השני ע"י ביצוע Index Fast Full Scan במקום Full Table Scan משמעותי יותר.

אתם מוזמנים להעיר הערות או לשאול שאלות בעמוד הזה, או לכתוב לי ל-oren@db-oriented.com

בעיה אחת – פתרונות רבים

Oren Nakdimon 1 Reply

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

בגרסה 12c של אורקל נוספו מספר תכונות שמאפשרות לנו לכתוב פחות ולקבל את אותה הפונקציונליות, כמו למשל Lateral Inline Views או Row Limiting clause.

הנה דוגמה נחמדה לבעיה אחת עם מספר פתרונות.

לפני מספר ימים, Lucas Jellema מ- AMIS העלה כאן שאלה על "אריזה" של מספר רשומות המייצגות תקופות זמן סמוכות לרשומה אחת המייצגת את תקופת הזמן המאוחדת, והציע את הפתרון הבא, אשר מציג בצורה טובה מספר תכונות חשובות, כמו הפונקציות האנליטיות LAG ו-RANK ,Subquery Factoring (תכונה קיימת מגרסה 9i), וRecursive Subquery Factoring (תכונה שנוספה בגרסה 11g):

with chairpeople as
( select chairperson
  ,      date_from
  ,      date_to
  ,      case date_from - lag(date_to) over (partition by chairperson order by date_from asc)
         when 1 then 'PACK'
         end action
  ,      rank()  over (partition by chairperson order by date_from asc) rnk
  from   chairmanships
)
, packing_chairs (chair, date_from, date_to, lvl) as
( select chairperson, date_from, date_to, 1
  from   chairpeople
  where  action is null
  union all
  select p.chair, p.date_from, c.date_to, lvl+1
  from   chairpeople c
         join
         packing_chairs p
         on (c.chairperson = p.chair and c.rnk = p.lvl+1)
  where  c.action='PACK'
  )
, packed_chairs as
( select chair, date_from, nullif(max(nvl(date_to,date'9999-12-31')),date'9999-12-31') date_to
  from   packing_chairs
  group
  by     chair, date_from
)
select *
from   packed_chairs
order
by     date_from;

-- הערה: זו גרסה מעט שונה של השאילתה מהפוסט המקורי

אני הצעתי פתרון אחר, המבוסס על הפונקציה האנליטית LAST_VALUE:

select chairperson,
       date_from,
       max(date_to) keep(dense_rank last order by date_to) date_to
from   (select chairperson,
               last_value(new_period_date_from ignore nulls) over(partition by chairperson order by date_from) date_from,
               date_to
        from   (select chairperson,
                       date_from,
                       date_to,
                       case when lnnvl(date_from – lag(date_to) over(partition by chairperson order by date_from) = 1) then date_from end new_period_date_from
                from   chairmanships))
group  by chairperson,
          date_from
order  by date_from;

בהערה אחרת לפוסט של Lucas, הציע Sayan Malakshinov פתרון פשוט עוד יותר:

select 
  chairperson
 ,min(date_from) keep (dense_rank first order by date_from,date_to) as date_from
 ,max(date_to  ) keep (dense_rank last  order by date_from,date_to) as date_to
from (
      select
           chairperson 
         , date_from 
         , date_to 
         , sum(flag) over(partition by chairperson order by date_from,date_to) grp
      from (
            select 
                 chairperson 
               , date_from 
               , date_to 
               , decode( 1 + lag(date_to)over(partition by chairperson order by date_from,date_to), date_from, 0, 1) flag
            from chairmanships
           )
     )
group by chairperson, grp
order by chairperson, grp;

ולבסוף (נכון לעכשיו לפחות), הצעתי פתרון נוסף, המשתמש בתכונה חדשה שנוספה בגרסה 12c של אורקל – Pattern Matching:

select * from chairmanships 
  match_recognize (
     partition by chairperson
     order by date_from
     measures frst.date_from as date_from,
              decode(classifier(),'NXT',nxt.date_to,frst.date_to) as date_to
     one row per match
     after match skip past last row
     pattern (frst nxt*)
     define nxt as nxt.date_from = prev(nxt.date_to)+1) 
order by chairperson,date_from;

אז הנה – ראינו שאלה אחת, וארבעה פתרונות שונים לחלוטין.