تفاوت ایندکس های 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 وجود دارند ایندکس تعریف کنید.

 

 

موفق باشید

یک بهینه سازی ساده اما کاربردی

سلام

اعتقاد دارم بسیاری از بهینه سازی های دستورات با تفکر صحیح نسبت به مسئله به دست می آید و خیلی از اوقات نوع نگرش و نحوه حل مسئله در نحوه عملکرد و نتیجه بسیار تاثیر گذار است. به عنوان مثال یکی از بهینه سازی های بسیار ساده که در جاهای مختلف نمونه هایی از آن را دیده ام و با یک تغییر بسیار ساده نتایج عالی گرفتیم را با شما در میان می گذارم.

فرض کنید جدولی داریم با صد هزار رکورد که تنها 10 رکورد آن دارای flag=Y است و بقیه رکوردها دارای مقدار flag=N هستند، این جدول را می سازیم:

create table Mytest_tbl

as

select rownum as "ID",decode(mod(rownum,10000),0,'Y','N') as my_flag from dual connect by level <= 100000;



select my_flag,count(*) from Mytest_tbl

group by my_flag;

 

برای ستون my_flag این جدول یک ایندکس می سازیم:

 

create index IDX_MY_FLAG on Mytest_tbl (MY_FLAG);

 

 

آمار جدول را به روز می کنیم:

begin

dbms_stats.gather_table_stats(

    ownname=> user,

    tabname=> 'MYTEST_TBL'

    ,Estimate_Percent  => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE

    ,Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO'

    ,Degree            => SYS.DBMS_STATS.DEFAULT_DEGREE

    ,Cascade           => TRUE

    ,No_Invalidate     => SYS.DBMS_STATS.AUTO_INVALIDATE);

end;

 

حالا می خواهیم مقادیری که ستون my_flag مقدار Y دارد را به دست آوریم، در نظر داشته باشید که این کار به دو روش امکان پذیر است، یکی اینکه بگوییم My_Flag = ’Y’ و دیگری My_Flag != ’N’

با اینکه هر دو شرط درست هستند و نتایج یکسانی به ما می دهند، اما در واقع عملکرد متفاوتی دارند:

روش اول (My_Flag!=’N’)

select * from Mytest_tbl t

where t.MY_Flag != 'N';


---------------------------------------------------------------------------

| Id  | Operation           | Name       | Rows | Bytes | Cost | Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |            |   10 |    70 |   47 | 00:00:01 |

| * 1 |   TABLE ACCESS FULL | MYTEST_TBL |   10 |    70 |   47 | 00:00:01 |

---------------------------------------------------------------------------


Predicate Information (identified by operation id):

------------------------------------------

* 1 - filter("T"."MY_FLAG"<>'N')

 

روش دوم (My_Flag=’Y’):

select * from Mytest_tbl t

where t.MY_Flag = 'Y';


----------------------------------------------------------------------------------------------

| Id  | Operation                             | Name        | Rows | Bytes | Cost | Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                      |             |   10 |    70 |    2 | 00:00:01 |

|   1 |   TABLE ACCESS BY INDEX ROWID BATCHED | MYTEST_TBL  |   10 |    70 |    2 | 00:00:01 |

| * 2 |    INDEX RANGE SCAN                   | IDX_MY_FLAG |   10 |       |    1 | 00:00:01 |

----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

------------------------------------------

* 2 - access("T"."MY_FLAG"='Y')

 

همانطور که در Plan اجرای هر دو دستور مشخص است، وقتی MY_Flag = 'Y' است از ایندکس استفاده می شود ولی در حالت MY_Flag != 'N' از ایندکس استفاده نمی شود و هزینه اجرای آنها بسیار متفاوت است.

بسیاری از بهینه سازی ها با بهینه سازی نحوه تفکر ما حاصل می شوند.

 

موفق باشید