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

החל מגרסה 12, אורקל עושים זאת שוב ופותרים לנו את הבעיה בהינף פקודה. הפקודה החדשה היא הרחבה של פקודת ה-Select הרגילה והיא נראית ככה:

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

למעדיפים את התיעוד הרשמי, אז זה נמצא כאן.

אז מה יש לנו כאן?

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

בוא נראה כמה דוגמאות לשימוש.

נניח שאנחנו רוצים לשלוף את 5 הרשומות הראשונות בטבלה, התחביר הוא מאוד פשוט:

SELECT val
FROM   top_n_example
ORDER BY val
FETCH FIRST 5 ROWS ONLY;
 
       VAL
----------
         1
         2
         3
         4
         5
 
5 rows selected.

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

רגע, אבל מה קורה אם אנחנו רוצים לקבל את כל הרשומות בעלות 5 הערכים הנמוכים ביותר? איך נבטיח שיגיעו כל הרשומות במקרה הזה?

SELECT val
FROM   top_n_example
ORDER BY val
FETCH FIRST 5 ROWS WITH TIES;
 
       VAL
----------
         1
         2
         3
         4
         5
         5
         5
 
7 rows selected.

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

SELECT val
FROM   top_n_example
ORDER BY val
FETCH FIRST 10 PERCENT ROWS ONLY;
 
       VAL
----------
         1
         2
         3
         4
 
4 rows selected.

דפדוף

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

דוגמה לשליפה הבעייתית:

SELECT val
FROM   (SELECT val, rownum AS r
        FROM   (SELECT val
                FROM   top_n_example)
        WHERE rownum < 40)
WHERE  r >= 21;

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

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

SELECT val
FROM   top_n_example
ORDER BY val
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

אנחנו מבקשים מאורקל להתחיל מ-offset מסויים ואז להביא את 20 הרשומות שאחריו. כדאי לשים לב שה-offset הוא מספר הרשומות שאותן אנחנו לא מציגים – כך שאם רוצים להתחיל ברשומה 21 אז צריך לשים offset של 20. למעשה, ברירת המחדל היא 0 כך שאם לא רשמנו כלום אז הנחנו שמתחילים מהרשומה הראשונה בתוצאה של השליפה.

דברים נוספים שכדאי לשים אליהם לב:

1. אי אפשר לשים ערכים שליליים ב-offset או ב-next (זה יתנהג כאילו כתבנו 0)
2. אם שמים null במקום מספרים ב-offset או ב-next אז נקבל no data found
3. אם שמים ערך עם נקודה עשרונית הוא מעגל למטה את הערך
4. אם שמים offset שהוא גדול ממספר הרשומות בטבלה נקבל no data found אבל אם נשים מספר שגדול ממספר הרשומות בטבלה ב-next אז נקבל את כל הרשומות בטבלה.
5. לא ניתן להשתמש בתחביר הזה בשביל materialized views או כשאנחנו עושים select for update.
6. מכוון שזה ה-syntax הרגיל של שליפה, הערכים שב-offset או ב-next יכולים להיות bind variables

הערה: שימו לב שהתחביר מאוד גמיש: ניתן להשתמש הן במילה rows והן במילה row (וגם ב-first ו-next). זה לא ממש משנה את הפעולה עצמה אבל שווה לבחור אחד מהם ולהשתמש תמיד בו – זה יעשה את העניינים פחות מבלבלים… אני בחרתי ב-next ו-rows… 🙂