فهرسة Order By

لا تحتاج استعلامات SQL التي فيها جملة order by إلى فرز النتيجة صراحةً إذا كان الفهرس المعني يعيد الصفوف بالترتيب المطلوب أصلاً. ويعني ذلك أن الفهرس نفسه المستخدم لجملة where يجب أن يغطي جملة order by أيضاً.

وكمثال، تأمّل الاستعلام التالي الذي يختار مبيعات الأمس مرتَّبةً بتاريخ البيع ومعرّف المنتج:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date, product_id

يوجد بالفعل فهرس على SALE_DATE يمكن استخدامه لجملة where. غير أن قاعدة البيانات يجب أن تنفّذ عملية فرز صريحة لتلبية جملة order by:

Db2 (LUW)

Explain Plan
------------------------------------------------------------
ID | Operation             |                     Rows | Cost
 1 | RETURN                |                          |  682
 2 |  TBSCAN               |     394 of 394 (100.00%) |  682
 3 |   SORT                |     394 of 394 (100.00%) |  682
 4 |    FETCH SALES        |     394 of 394 (100.00%) |  682
 5 |     IXSCAN SALES_DATE | 394 of 1009326 (   .04%) |   19

Predicate Information
 5 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
      STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))

وغُيِّرت جملة where هكذا لتتوافق مع Db2: WHERE sale_date > CURRENT_DATE - 1 DAY.

Oracle

---------------------------------------------------------------
|Id | Operation                    | Name       | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT             |            |  320 |   18 |
| 1 |  SORT ORDER BY               |            |  320 |   18 |
| 2 |   TABLE ACCESS BY INDEX ROWID| SALES      |  320 |   17 |
|*3 |    INDEX RANGE SCAN          | SALES_DATE |  320 |    3 |
---------------------------------------------------------------

وعلى أي حال يعيد INDEX RANGE SCAN النتيجة بترتيب الفهرس. ولاستغلال هذه الحقيقة، علينا فقط توسيع تعريف الفهرس بحيث يوافق جملة order by:

  DROP INDEX sales_date

CREATE INDEX sales_dt_pr ON sales (sale_date, product_id)

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date, product_id

Db2 (LUW)

Explain Plan
-----------------------------------------------------------
ID | Operation            |                     Rows | Cost
 1 | RETURN               |                          |  688
 2 |  FETCH SALES         |     394 of 394 (100.00%) |  688
 3 |   IXSCAN SALES_DT_PR | 394 of 1009326 (   .04%) |   24

Predicate Information
 3 - START (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))
      STOP (Q1.SALE_DATE = (CURRENT DATE - 1 DAYS))

Oracle

---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |  320 |  300 |
| 1 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  300 |
|*2 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

اختفت عملية الفرز SORT ORDER BY من خطة التنفيذ رغم أن الاستعلام لا يزال يحتوي جملة order by؛ إذ تستغل قاعدة البيانات ترتيب الفهرس وتتخطى عملية الفرز الصريحة.

مهم

إذا وافق ترتيب الفهرس جملة order by، استطاعت قاعدة البيانات إسقاط عملية الفرز الصريحة.

ومع أن خطة التنفيذ الجديدة فيها عمليات أقل، فقد ارتفعت قيمة التكلفة ارتفاعاً كبيراً لأن عامل العنقدة في الفهرس الجديد أسوأ (انظر «عامل العنقدة المُحسَّن تلقائياً»). ويكفي هنا أن نلاحظ أن قيمة التكلفة ليست دائماً مؤشراً جيداً على جهد التنفيذ.

عامل العنقدة المُحسَّن تلقائياً

تبقي قاعدة بيانات Oracle عامل العنقدة في حده الأدنى بمراعاة ROWID في ترتيب الفهرس؛ فكلما كان لمدخلَي فهرس قيمتا مفتاح متماثلتان، حسم ROWID ترتيبهما النهائي. ويكون الفهرس إذن مرتَّباً وفق ترتيب الجدول أيضاً، وله أصغر عامل عنقدة ممكن لأن ROWID يمثّل العنوان الفيزيائي لصف الجدول.

وبإضافة عمود آخر إلى فهرس، تُدخل معيار فرز جديداً قبل ROWID؛ فتقل حرية قاعدة البيانات في مواءمة مدخلات الفهرس مع ترتيب الجدول، فلا يمكن لعامل العنقدة إلا أن يسوء.

ومع ذلك، لا يزال ممكناً أن يوافق ترتيب الفهرس ترتيب الجدول إجمالاً؛ فمبيعات اليوم الواحد محتمل أن تبقى متجمّعة في الجدول كما في الفهرس — حتى لو لم يعد تسلسلها متماثلاً تماماً. ويجب على قاعدة البيانات قراءة كتل الجدول مرات عدة عند استخدام فهرس SALE_DT_PR — لكنها الكتل نفسها كما في السابق. وبفضل التخزين المؤقت للبيانات كثيرة الوصول، قد يكون أثر الأداء أقل بكثير مما تدل عليه قيم التكلفة.

ويكفي لهذا التحسين أن يكون نطاق الفهرس الممسوح مرتَّباً وفق جملة order by. ولذلك يعمل التحسين أيضاً في هذا المثال تحديداً عند الترتيب حسب PRODUCT_ID وحده:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY product_id

في الشكل 6.1 نرى أن PRODUCT_ID هو معيار الفرز الوحيد ذو الصلة في نطاق الفهرس الممسوح. ومن ثمّ يوافق ترتيب الفهرس جملة order by في نطاق الفهرس هذا، فتستطيع قاعدة البيانات إسقاط عملية الفرز.

الشكل 6.1 ترتيب الفرز في نطاق الفهرس ذي الصلة

وقد يسبّب هذا التحسين سلوكاً غير متوقع عند توسيع نطاق الفهرس الممسوح:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY product_id

لا يسترجع هذا الاستعلام مبيعات الأمس بل جميع المبيعات منذ الأمس؛ أي إنه يغطي عدة أيام ويمسح نطاق فهرس غير مرتَّب حصراً بـPRODUCT_ID. وإذا نظرنا إلى الشكل 6.1 مرة أخرى ووسّعنا نطاق الفهرس الممسوح إلى الأسفل، نرى أن هناك قيم PRODUCT_ID أصغر مجدداً. ولذلك يجب على قاعدة البيانات استخدام عملية فرز صريحة لتلبية جملة order by.

Db2 (LUW)

Explain Plan
-------------------------------------------------------------
ID | Operation              |                     Rows | Cost
 1 | RETURN                 |                          |  688
 2 |  TBSCAN                |     394 of 394 (100.00%) |  688
 3 |   SORT                 |     394 of 394 (100.00%) |  688
 4 |    FETCH SALES         |     394 of 394 (100.00%) |  688
 5 |     IXSCAN SALES_DT_PR | 394 of 1009326 (   .04%) |   24

Predicate Information
 5 - START ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)

Oracle

---------------------------------------------------------------
|Id |Operation                    | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT             |             |  320 |  301 |
| 1 | SORT ORDER BY               |             |  320 |  301 |
| 2 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  300 |
|*3 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

وإذا استخدمت قاعدة البيانات عملية فرز رغم توقعك تنفيذاً متدفقاً، فقد يكون لذلك سببان: (1) خطة التنفيذ ذات عملية الفرز الصريحة لها قيمة تكلفة أفضل؛ (2) ترتيب الفهرس في نطاق الفهرس الممسوح لا يوافق جملة order by.

إن أعجبك هذا الموضوع، قد يعجبك أيضاً…

… أن تشترك في القوائم البريدية، وتحصل على ملصقات مجانية، وتشتري كتابي، أو تنضم إلى دورة تدريبية.

وثمة طريقة بسيطة للتمييز بين الحالتين: استخدام تعريف الفهرس الكامل في جملة order by — أي تعديل الاستعلام ليوافق الفهرس بغية إزالة السبب الثاني. فإن استخدمت قاعدة البيانات عملية فرز صريحة بعد ذلك، فالمُحسِّن يفضّل هذه الخطة بسبب قيمة تكلفتها؛ وإلا فقاعدة البيانات لا تستطيع استخدام الفهرس لجملة order by الأصلية.

نصيحة

استخدم تعريف الفهرس الكامل في جملة order by لتعرف سبب عملية الفرز الصريحة.

وفي كلتا الحالتين، قد تتساءل إن كان ممكناً الوصول إلى تنفيذ order by متدفق، وكيف. ولذلك يمكنك تنفيذ الاستعلام بتعريف الفهرس الكامل في جملة order by وفحص النتيجة. وستدرك غالباً أن تصورك للفهرس خاطئ وأن ترتيب الفهرس بالفعل ليس كما تتطلبه جملة order by الأصلية، فلا تستطيع قاعدة البيانات استخدام الفهرس لتجنّب عملية الفرز.

وإذا فضّل المُحسِّن عملية فرز صريحة بسبب قيمة تكلفتها، فمردّ ذلك عادةً إلى أن المُحسِّن يأخذ أفضل خطة تنفيذ للتنفيذ الكامل للاستعلام؛ أي إنه يختار خطة التنفيذ الأسرع للوصول إلى السجل الأخير. أما إذا اكتشف أن التطبيق يجلب الصفوف الأولى فقط، فقد يفضّل بدوره order by مفهرساً. ويشرح الفصل 7النتائج الجزئية أساليب التحسين المقابلة.