فهرسة `ASC` و`DESC` و`NULLS FIRST`/`LAST`
تستطيع قواعد البيانات قراءة الفهارس في الاتجاهين. ويعني ذلك أن order by متدفقاً ممكن أيضاً إذا كان نطاق الفهرس الممسوح بالترتيب المعاكس تماماً لما تحدده جملة order by. ومع أن مُعدِّلَي ASC وDESC في جملة order by قد يمنعان التنفيذ المتدفق، تقدّم معظم قواعد البيانات طريقة بسيطة لتغيير ترتيب الفهرس ليصبح قابلاً للاستخدام في order by متدفق.
يستخدم المثال التالي فهرساً بترتيب معاكس، وهو يعرض المبيعات منذ الأمس مرتَّبة بتاريخ تنازلي وPRODUCT_ID تنازلي.
SELECT sale_date, product_id, quantity
FROM sales
WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY sale_date DESC, product_id DESC
وتُظهر خطة التنفيذ أن قاعدة البيانات تقرأ الفهرس في اتجاه تنازلي.
Db2 (LUW)
Explain Plan
---------------------------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 688
2 | FETCH SALES | 394 of 394 (100.00%) | 688
3 | IXSCAN (REVERSE) SALES_DT_PR | 394 of 1009326 ( .04%) | 24
Predicate Information
3 - STOP ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)
وفي Db2 يمكن منع المسوحات المعاكسة باستخدام جملة DISALLOW REVERSE SCAN أثناء إنشاء الفهرس.
Oracle
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 320 | 300 |
| 1 | TABLE ACCESS BY INDEX ROWID | SALES | 320 | 300 |
|*2 | INDEX RANGE SCAN DESCENDING| SALES_DT_PR | 320 | 4 |
---------------------------------------------------------------
وفي هذه الحالة تستخدم قاعدة البيانات شجرة الفهرس للعثور على المدخل المطابق الأخير، ثم تتبع سلسلة العقد الورقية «صعوداً» كما في الشكل 6.2. وهذا في نهاية المطاف هو السبب في استخدام قاعدة البيانات قائمة مزدوجة الترابط لبناء سلسلة العقد الورقية.
الشكل 6.2 مسح الفهرس بالاتجاه المعاكس
ومن الجوهري طبعاً أن يكون نطاق الفهرس الممسوح بالترتيب المعاكس تماماً كما تحتاجه جملة order by.
مهم
تستطيع قواعد البيانات قراءة الفهارس في الاتجاهين.
ولا يحقق المثال التالي هذا الشرط المسبق لأنه يخلط مُعدِّلَي ASC وDESC في جملة order by:
SELECT sale_date, product_id, quantity
FROM sales
WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
ORDER BY sale_date ASC, product_id DESC
فيجب أن يعرض الاستعلام أولاً مبيعات الأمس مرتَّبة بـPRODUCT_ID تنازلياً، ثم مبيعات اليوم، أيضاً بـPRODUCT_ID تنازلياً. ويوضح الشكل 6.3 هذه العملية. وللحصول على المبيعات بالترتيب المطلوب، يجب على قاعدة البيانات أن «تقفز» أثناء مسح الفهرس.
الشكل 6.3 order by متدفق مستحيل
غير أنه لا يوجد رابط في الفهرس من مبيعة الأمس ذات أصغر PRODUCT_ID إلى مبيعة اليوم ذات أكبر PRODUCT_ID. ولذلك لا تستطيع قاعدة البيانات استخدام هذا الفهرس لتجنّب عملية فرز صريحة.
وفي حالات كهذه، تقدّم معظم قواعد البيانات طريقة بسيطة لمواءمة ترتيب الفهرس مع جملة order by. وبشكل ملموس، يعني ذلك أنه يمكنك استخدام مُعدِّلَي ASC وDESC في تعريف الفهرس:
DROP INDEX sales_dt_pr
CREATE INDEX sales_dt_pr
ON sales (sale_date ASC, product_id DESC)
تحذير
قبل الإصدار 8.0، كانت قاعدة بيانات MySQL تتجاهل مُعدِّلَي ASC وDESC في تعريف الفهرس. ولا تحترم MariaDB DESC في الفهارس إلا منذ الإصدار 10.8.
والآن يوافق ترتيب الفهرس جملة order by فتستطيع قاعدة البيانات إسقاط عملية الفرز:
Db2 (LUW)
Explain Plan
-----------------------------------------------------------
ID | Operation | Rows | Cost
1 | RETURN | | 675
2 | FETCH SALES | 387 of 387 (100.00%) | 675
3 | IXSCAN SALES_DT_PR | 387 of 1009326 ( .04%) | 24
Predicate Information
3 - START ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)
Oracle
---------------------------------------------------------------
|Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 320 | 301 |
| 1 | TABLE ACCESS BY INDEX ROWID| SALES | 320 | 301 |
|*2 | INDEX RANGE SCAN | SALES_DT_PR | 320 | 4 |
---------------------------------------------------------------
ويعرض الشكل 6.4 ترتيب الفهرس الجديد. وتغيير اتجاه الفرز للعمود الثاني يبادل بمعنى ما اتجاه الأسهم في الشكل السابق، فينتهي السهم الأول حيث يبدأ الثاني، ويكون للفهرس الصفوف بالترتيب المطلوب.
مهم
عند استخدام مُعدِّلَي ASC وDESC مختلطَين في جملة order by، يجب تعريف الفهرس بالطريقة نفسها لاستخدامه في order by متدفق.
ولا يؤثر ذلك في قابلية استخدام الفهرس لجملة where.
الشكل 6.4 فهرس بترتيب مختلط
ولا تلزم فهرسة ASC/DESC إلا لفرز أعمدة فردية بالاتجاه المعاكس، ولا تلزم لعكس ترتيب جميع الأعمدة لأن قاعدة البيانات تستطيع مع ذلك قراءة الفهرس بترتيب تنازلي عند الحاجة — والاستثناء الوحيد هو الفهارس الثانوية على الجداول المنظَّمة بالفهرس. فالفهارس الثانوية تضيف ضمناً مفتاح العنقدة إلى الفهرس دون توفير أي إمكانية لتحديد ترتيب الفرز. وإذا احتجت إلى فرز مفتاح العنقدة بترتيب تنازلي، فليس لديك خيار سوى فرز جميع الأعمدة الأخرى بترتيب تنازلي، ثم تقرأ قاعدة البيانات الفهرس بالاتجاه المعاكس للحصول على الترتيب المطلوب.
إن أعجبك هذا الموضوع، قد يعجبك أيضاً…
… أن تشترك في القوائم البريدية، وتحصل على ملصقات مجانية، وتشتري كتابي، أو تنضم إلى دورة تدريبية.
وإلى جانب ASC وDESC، يعرّف معيار SQL مُعدِّلَين لا يكادان يُعرفان لجملة order by: NULLS FIRST وNULLS LAST. وقد أُدخل التحكم الصريح في فرز NULL «حديثاً» كامتداد اختياري مع SQL:2003. ونتيجة لذلك، دعم قواعد البيانات ضعيف. وهذا مقلق بوجه خاص لأن المعيار لا يعرّف بدقة ترتيب فرز NULL؛ فهو يكتفي بأن جميع قيم NULL يجب أن تظهر معاً بعد الفرز، دون تحديد ما إذا كان ينبغي أن تظهر قبل المدخلات الأخرى أو بعدها. ومن الناحية الدقيقة، ستحتاج فعلاً إلى تحديد فرز NULL لجميع الأعمدة التي يمكن أن تكون NULL في جملة order by للحصول على سلوك محدد جيداً.
لكن الواقع أن هذا الامتداد الاختياري غير مطبَّق في SQL Server 2019 ولا في MySQL 8.0. أما قاعدة بيانات Oracle، على العكس، فقد كانت تدعم فرز NULLS قبل إدخاله إلى المعيار، لكنها لا تقبله في تعريفات الفهارس حتى الإصدار 19c. ولذلك لا تستطيع قاعدة بيانات Oracle تنفيذ order by متدفق عند الفرز بـNULLS FIRST. ولا تدعم المُعدِّل NULLS في جملة order by وفي تعريف الفهرس معاً سوى قاعدة بيانات PostgreSQL (منذ الإصدار 8.3).
وتلخّص النظرة العامة التالية الميزات التي تقدّمها قواعد البيانات المختلفة.
الشكل 6.5 مصفوفة قواعد البيانات/الميزات