آیا ترتیب تعریف ستون های یک جدول تاثیری بر عملکرد کوئری ها دارد

یکی از سوالاتی که برای بعضی از دوستان پیش می آید این است که آیا ترتیب تعریف ستون های یک جدول تاثیری بر عملکرد کوئری ها دارد؟

برای پاسخ به این سوال، به نحوه ذخیره سازی سطرهای جداول در یک block توجه فرمایید که به شکل زیر است:

Dn

Ln

…

D3

L3

D2

L2

D1

L1

H

 

 

H: Header

Ln: Length of Column n

Dn: Data of Column n

این نکات را در نظر داشته باشید:

• خواندن block به شکل سریال است، پس برای خوانده شدن اطلاعات ستون دوم، به ناچار ستون اول هم خوانده می شود و ...
• برای مقادیر null چیزی ذخیره نمی شود.

پس با توجه به موارد بالا، در طراحی جدول این نکات را در نظر داشته باشید:

1. ستون هایی را که خیلی استفاده می شوند را در ابتدا تعریف کنید. (به علت سریالی خوانده شدن block). تاثیر این نکته وقتی بیشتر مشخص می شود که جدولی داریم با ستون های زیاد و ما فقط چند ستون آن را به تناوب نیاز داریم.
2. تنها ستون هایی را در دستور select بیاورید که واقعاً مورد نیاز هستند.
3. ستون های یک جدول که می توانند مقادیر null داشته باشند را در انتهای ستون های جدول تعریف کنید.

حالا نگاهی هم به هزینه اجرای دستورات می اندازیم. برای این کار جدول test_cost_tbl را با پنج ستون می سازیم:
 

drop table test_cost_tbl purge;

CREATE TABLE test_cost_tbl(
        col_1 NUMBER,
        col_2 NUMBER,
        col_3 NUMBER,
        col_4 NUMBER,
        col_5 NUMBER);

سپس یک سطر در این جدول insert و آمار جدول را نیز بروز رسانی میکنیم:

INSERT INTO test_cost_tbl VALUES (1, 2, 3, 4, 5);

commit;

begin
  dbms_stats.gather_table_stats(user,'test_cost_tbl');
end;

برای خواندن هر کدام از ستون های جدول به صورت مجزا و تهیه Plan اجرای آنها از دستورات زیر استفاده می کنیم:

EXPLAIN PLAN SET STATEMENT_ID 'col_1' FOR SELECT col_1 FROM test_cost_tbl;
EXPLAIN PLAN SET STATEMENT_ID 'col_2' FOR SELECT col_2 FROM test_cost_tbl;
EXPLAIN PLAN SET STATEMENT_ID 'col_3' FOR SELECT col_3 FROM test_cost_tbl;
EXPLAIN PLAN SET STATEMENT_ID 'col_4' FOR SELECT col_4 FROM test_cost_tbl;
EXPLAIN PLAN SET STATEMENT_ID 'col_5' FOR SELECT col_5 FROM test_cost_tbl;

با بررسی مقادیر ثبت شده در جدول plan_table می توانیم مشاهده کنیم که هزینه CPU خواندن از ستون ها با هم متفاوت است (20 عدد به ازا هر ستون به هزینه CPU اضافه می شود) اما هزینه I/O آنها ثابت مانده است. علت ثابت ماندن هزینه I/O احتمالاً این است که کل رکورد در یک بلاک ذخیره شده اند و برای خواندن آن نیاز به I/O اضافی نیست.

SELECT statement_id, cpu_cost as total_cpu_cost,cpu_cost-lag(cpu_cost) OVER (ORDER BY statement_id) AS cpu_cost_diff, io_cost
FROM plan_table
WHERE id = 0
ORDER BY statement_id;

خروجی دستور بالا به شکل تصویر زیر قابل مشاهده است:

 

پی نوشت:

این مطلب و مثال را چند سال پیش از یک وبلاگ خواندم و مستند کردم. متاسفانه آن وبلاگ را پیدا نکردم تا ارجاع بدهم که در این مورد عذرخواهی میکنم.

 

موفق باشید

تفاوت ایندکس های Invisible و Unusable

تفاوت ایندکس های Invisible با Unusable

برخی از دوستان مفاهیم Unusable و Invisible را به شکل یکسان استفاده می کنند. اما تفاوت هایی وجود دارد که به طور کلی و خلاصه عرض می کنم:

ایندکس Unusable

• توسط Optimizer نادیده گرفته می شود و دستورات DML نیز آن را به روز نمی کنند و آمار هم برای آن ایندکس جمع آوری نمی شود.
• اگر قبل از انجام عملیات Bulk Load ایندکس های مربوطه را unusable کنیم، عملیات با سرعت بیشتری انجام می شود. دلیل این کار این است که به دلیل اینکه ایندکس ها به روزرسانی نمی شوند و عملیات با سرعت بیشتری انجام می شود.
• بعد از پایان کار Bulk Load می توانید با rebuild کردن ایندکس، آن را مجدداً با همان مشخصات داشته باشید.
• وقتی ایندکسی به صورت unusable ساخته می شود هیچ سگمنتی برای آن ایندکس اختصاص داده نمی شود.
• اگر ایندکس موجود را unusable کنید، سگمنت آن حذف می شود.

 

ایندکس Invisible

• دستورات DML آن را به روز رسانی می کنند.
• قبل از حذف ایندکس، برای بررسی تاثیر آن، می توانیم ابتدا ایندکس را invisible کنیم و اگر مشکلی نبود آن را حذف کنیم.
• می توانید ایندکس های مختلفی شامل ستون های تکراری بسازی ولی تنها یکی از آنها می تواند visible باشد و بقیه invisible که این کار به شما کمک می کند تست های مختلفی انجام دهید.

 

موفق باشید

نکاتی در خصوص ایندکس گذاری در دیتابیس اوراکل

سلام

همانطور که مستحضرید استفاده از ایندکس مناسب باعث بهبود عملکرد سیستم می شود. در زیر لیستی از نکات ایندکس گذاری به طور کلی بیان شده است، بدیهی است که انتخاب نوع ایندکس و جزئیات پیاده سازی مستلزم بررسی بیشتر می باشد.

در خصوص ایجاد ایندکس بر روی جداول موارد زیر پیشنهاد می گردند:

1. در حدی که واقعاً نیاز هست از ایندکس استفاده کنید و از ایجاد ایندکس های اضافی اجتناب نمایید. وجود ایندکس صحیح باعث بهبود برخی از دستورات SQL می شود ولی از طرفی باعث کاهش عملکرد در زمان ثبت اطلاعات می شود و همچنین فضای دیسک اضافی مصرف می شود.
2. برای ایندکس گذاری باید Query های اصلی جدول مورد نظرتان را خوب بدانید. انتخاب نوع و نحوه ایندکس گذاری بسیار به Query وابسته است. ایندکس گذاری روی ستون هایی از جدول که دائم در شرط Query استفاده می شوند تاثیر خوبی بر عملکرد دارد.
3. برای تمام جداول، کلید اصلی بسازید. با این کار به طور خودکار بر روی ستون یا ستون های کلید اصلی ایندکس ساخته می شود و این ایندکس در Join ها بسیار موثر است.
4. برای ستون های مربوط به کلید خارجی ایندکس بسازید. از آنجایی که معمولاً این ستون ها در شرط های join استفاده می شوند، ساخت ایندکس روی ستون های کلید خارجی عملکرد را بهبود می دهد. از طرفی ایجاد ایندکس روی ستون کلید خارجی موجب کاهش مشکل Lock روی جدول Parent به هنگام عملیات های Delete و Update می شود.
5. با توجه به کاربرد مورد نظر از نوع ایندکس مناسب استفاده نمایید. استفاده از ایندکس مناسب باعث افزایش عملکرد سیستم می گردد. لذا لازم است انواع ایندکس و کاربرد هر کدام از آنها را بدانید.
6. اگر در انتخاب نوع ایندکس شک دارید از ایندکس معمولی (B*Tree Index) استفاده کنید. در اکثر مواقع این نوع ایندکس پاسخگوی نیاز Application می باشد.
7. از Bitmap Index در سیستم های Warehouse استفاده کنید. استفاده از از Bitmap Index در محیط های OLTP بسیار بد می باشد و باعث ایجاد Lock در سیستم می شود. این ایندکس ها برای جاهایی مناسب می باشند که اطلاعات به ندرت به روز رسانی می شوند. همچنین در محیط های Warehouse در عملیات های منطقی AND و OR بسیار کاربردی می باشند.
8. ایندکس ها را بدون دلیل Rebuild نکنید. تنها زمانی باید این کار انجام شود که ایندکس unusable شده است و یا می خواهیم ایندکس را به Tablespace دیگر منتقل کنیم.
9. استفاده از ایندکس ها را مانیتور کنید و ایندکس های اضافی را پاک کنید. وجود ایندکس صحیح باعث بهبود برخی از دستورات SQL می شود ولی از طرفی باعث کاهش عملکرد در زمان ثبت اطلاعات می شود و همچنین فضای دیسک اضافی مصرف می شود.
10. قبل از پاک کردن ایندکس ابتدا آن را Invisible کنید. با این کار قبل از حذف ایندکس، مطمئن می شویم که این کار تاثیر بدی بر عملکرد سیستم ندارد.
11. اگر حجم دیتا برای insert خیلی زیاد است، ایندکس های را پس از insert کردن اطلاعات ایجاد کنید یا قبل از شروع bulk insert آنها را unusable کنید. تا عمل insert با سرعت بیشتری انجام شود و در نهایت آنها را rebuild کنید.
12. چنانچه یک ایندکس از چند ستون می سازید، ترتیب قرار گیری ستون ها به ترتیب استفاده باشد. ترتیب ستون های ایندکس در عملکرد تاثیر بسیار دارد. به عنوان مثال اگر شما روی ستون های A,B ایندکس ساخته باشید (ابتدا ستون A و سپس ستون B) و در دستورات ستون B را بیشتر استفاده می کنید، ممکن است تاثیر چندانی از ایجاد ایندکس مشاهده نکنید (عمل Index Skip Scan در این مورد اتفاق می افتد)
13. در نظر داشته باشید که در B*Tree Index (ایندکس های معمولی) مقداری برای null ثبت نمی شود و حتی با تعریف ایندکس بر روی این ستون ها، اگر مقادیر null در دستور Select حذف نشوند، از ایندکس استفاده نمی شود. به عنوان مثال در دو دستور زیر با وجود اینکه بر روی ستون manager_id ایندکس وجود دارد، از این ایندکس استفاده نمی شود:

Select * from hr.employees where manager_id is null;

Select * from hr.employees where manager_id is not null;

14. برای ستون هایی که دائماً در عبارات Order by, Group by, Union, Distinct وجود دارند ایندکس تعریف کنید.

 

 

موفق باشید