ایندکس در اوراکل

ایندکس

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

روش‌های دستیابی به داده‌های جدول

• Full table scan
• Index

ستون های جدول

• rowid آدرس 18 کاراکتری است که به محل آدرس فیزیکی محل ذخیره‌ سازی رکورد اشاره می کند
• ستون های جدول

ساختار ایندکس

ایندکس درختی متشکل از برگهایی است که هر برگ شامل دو مقدار key و value می باشد

Rowid = Object id + Data file id + Data block + Row number

Value = مقدار ستونی که بر روی آن ایندکس تعریف شده است

موارد استفاده از ایندکس

• در SQL هنگامی که در where Clause ها می‌خواهیم فیلتر انجام دهیم
• در مرتب سازی‌ها order by
• اگر در دستور select یکی از عبارت‌های order by ، group by ، distinct و union باشد از ایندکس استفاده خواهد شد.
• در دستور Join وجود ایندکس در ستون‌هایی که در شرط join شرکت می‌کنند باعث بهبود کارایی خواهد شد

نکته: هر دستور DML که باعث تغییر در داده‌های جدول می‌شود باعث به روز رسانی ایندکس می‌شود

در چه مواقعی بهتر است از ایندکس استفاده کنیم ؟

• اگر پرس و جو ها نوشته شده 5% از رگوردهای جدول را باز‌ می‌گرداند می‌توان بر روی ستونی که در where استفاده شده است ایندکس تعریف کرد
• برای ستون‌هایی که بصورت مکرر در where استفاده می‌شوند می‌توان ایندکس تعریف کرد
• پیشنهاد می‌شود که برای جداول PK (کلید اصلی) تعریف کنید ، تعریف PK در یک جدول باعث می‌شود که بصورت اتوماتیک یک unique index بر روی جدول تعریف شود
• پیشنهاد می‌شود برای ستون‌های FK در جداول ایندکس تعریف گردد، تعریف ایندکس بر روی ستون های FK باعث بهبود عملکرد دستور join خواهد شد
• برای بهینه سازی عملیاتی مانند order by ، group by ، distinct و union می‌توان از ایندکس استفاده کرد
• معمولا در سیستم‌های انبارداده (data warehouse) ها از ایندکس های بیشتر و در سیستم‌های OLTP به دلیل آنکه داده‌ها مرتب به روز رسانی می شود از Index های کمتری استفاده می‌شود

مواقعی که بهتر است از ایندکس استفاده نشود ؟

• ستون هایی که دارای مقادیر null و تکرای زیاد هستند.
• ستون هایی که تنوع داده ای کمی دارند مانند زن و مرد (در این حالت بهتر است که از ایندکس bitmap استفاده شود)
• ستون هایی که نوع داده ای آنها کاراکتری و یا Datetime است
• جداول کوچک (جداولی که تعداد رکوردهای کمی دارند)
• جداولی که حجم تغییرات داده در آنها زیاد است (داده ها در بازه‌ زمانی کم دائما در حال اضافه ، اصلاح یا حذف هستند)

انواع ایندکس

• Btree
  • ایندکس Btree یک درخت متوازن سه یا چهار سطحی می باشد که شامل برگ هایی است که هر برگ شامل دو مولفه key و value است
  • اولین کاربرد ایندکس در where clause ها است و دومین کاربرد ایتدکس در مرتب سازی است، هنگامی که در یک پرس و جو از order by ، group by ، distinct و unionاستفاده می کنید اوراکل می‌تواند از ایندکس استفاده کند
  • استفاده از عملگرهای منطقی and و or در دستورات SQL باعث می‌شود که از ایندکس btree استفاده نشود
  • در درخت ایندکس از نوع btree مقادیر null ذخیره نمی‌شوند بنابراین در پرس و جوهایی که در آنها از whrer column_name is null استفاده شده است کل جداول مورد پیمایش قرار داده می شود.
  • اوراکل هر زمانی که بتواند از ایندکس استفاده می‌کند، در واقع هر زمانی که بتواند با استفاده از ایندکس عملکرد بهتری ارائه دهد از ایندکس استفاده خواهد شد.
  • کاربرد دیگر ایندکس در Join ها است، معمولا بر روی ستون های FK جداول ایندکس تعریف می‌شود.

create index <index_name> on <table_name> (column_name) tablespace <tablespace_name> local parallel degree (DOP);

• Bitmap
  • برای محیط های انبارداده (data warehouse) استفاده از ایندکس bitmap مناسب نیست چون بیشتر پرس و جوهایی که در سیستم‌های انبارداده به پایگاه داده ارسال می‌گردد با استفاده از عملگر منطقی or یا and است، در این مواقع بهتر است به جای تعریف Btree Index از ایندکس bitmap استفاده شود
  • در ایندکس bitmap مقادیر null نیز به عنوان یک مقدار مجزا در نظر گرفته می‌شود
  • در دستورات SQL که از عملگرهای منطقی or و and استفاده شده است وجود ایندکس bitmap باعث بهبود کارایی خواهد شد

در چه مواقعی که بهتر است از ایندکس bitmap استفاده شود

• تنوع مقادیر(Cardinality) مقادیر کم است
• تعداد رکوردها زیاد است
• در دستورات SQL از عملیات منطقی (and و or) استفاده شده است

create bitmap index on <table_name> (column_name) tablespace <tablespace_name> local parallel degree (DOP);

اتواع Index از لحاظ نوع ایجاد

Unique

اوراکل در زمان ایجاد کلید اصلی (primary key) به صورت اتوماتیک یک unique index تعریف می‌کند.

create unique Index <index_name> On <table_name> (column_name);

Reverse

در مواقعی کاربرد دارد که قصد داریم برای فیلدی که مقدار آن بصورت ترتیبی اضاقه می‌شود Index تعریف کنیم، با استفاده از این گزینه محل قرار گرفتن برگ مربوط به رکورد را در درخت ایندکس پخش می‌کنیم.

Compress

مقادیر تکراری در ستون Index فقط یکبار ذخیره می شوند برای ایندکس های غیر یکتا (non-unique) که ممکن است مقادیر تکراری زیادی داشته باشند کاربرد دارد.

create index <index_name> on <table_name> (column_name)  tablespace <tablespace_name> comperess level <level_number>;

level_number: سطحی که قرار است فشرده سازی براساس آن انجام شود

در مواقعی است که برای بیش از یک ستون ایندکس ساخته می شود، ترتیب قرار گرفتن ستون ها در ایندکس مهم است و فیلدهایی باید در ابتدا قرار گیرد که به تنهایی بیشترین احتمال استفاده در  where clause ها را دارند

Function Index

می‌توانید با استفاده از یک تابع (Function)  بر روی یک فیلد ایندکس تعریف کنید.

Rebuild index

با انجام کارهای DML ایندکس به صورت اتوماتیک به روز رسانی می شود مخصوصاً پس از انجام عملیات حذف (delete)، در صورت نیاز می‌توان بصورت دستی نیز ایندکس را rebuild کرد

alter index <index_name> rebuild online nologging;

online : هنگام rebuild یک جدول ممکن است انجام عملیات DML یا DDL فراهم نباشد با استفاده از گزینه online در زمان rebuild کردن ایندکس امکان انجام دستورات DML در اختیار کاربران قرار خواهد گرفت، انجام دستورات DDL تا پایان rebuild ایندکس امکان پذیر نیست.

nologging: این گزینه باعث می‌شود کمترین log در online redo log ها ثبت شود، استفاده از این گزینه باعث بهبود سرعت rebuild ایندکس خواهد شد.

انتقال Index به Tablespace جدید

alter index <index_name> rebuild tablespace <new_tablespace_name>

ایندکس های unusable

انجام عملیات مانند انتقال داده‌ها و غیره ممکن است وضعیت یک ایندکس را به حالت unusable تغییر دهد که در این حالت اوراکل از ایندکس فوق استفاده نمی‌کند، در نسخه‌های قبلی اوراکل اگر در هنگام اجرای SQL با ایندکس unusable مواجه می‌شد دستور SQL خطا می‌داد ولی در نسخه‌های جدید اوراکل بصورت پیش فرض هنگام مواجه شدن با ایندکس unusable خطا نمایش داده نمی‌شود

اجرای دستور skip_unuasble_Index=false باعث می‌شود که همانند نسخه‌‌های قبلی هنگام مواجه شدن با ایندکس unusable خطا ایجاد شود.

select * from dba_indexses  where status = ‘unuasble’;

تغییر نام Index

از دستور ذیل برای تغییر نام ایندکس استفاده می‌شود

alter index < old_index_name> Rename to <new_index_name>;

حذف Index

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

drop Index <Index_name>;

ایندکس‌های پارتیشن شده

دو نوع ایندکس برای جداول پارتیشن شده وجود دارد

• Global
  • با استفاده از این روش می‌توان یک ایندکس را به روشی متفاوت از پارتیشن‌های جدول پارتیش بندی کرد یا حتی ایندکس را پارتیشن بندی نکرد.
  • نکته : هنگام اجرای دستورات DDL در جدول پارتیشن بندی شده ایندکس‌های Global ، unusable شده و باید مجدداً آن را rebuild کرد برای جلوگیری از این کار می‌توان از دستور update global index استفاده کرد.

alter table <table_name> drop partition <partition_name> update global index;

• Local

در هر پارتیشن تعریف می شوند و دارای مزایای ذیل می باشد

• با تغییرات داده ای توسط دستورات DML فقط ایندکس های پارتیشن(هایی) که در حال تغییر است rebuild می‌شود
• اوراکل می تواند از Plan های بهتری جهت اجرای SQL ها استفاده کند
• با استفاده از ایندکس‌های local می‌توان ایندکس‌های یک پارتیشن خاص را rebuildکرد.

Data Dictionary های مربوط به ایندکس‌ها

user_ind_columns

user_indexes

user_ind_statistics

user_ind_partitions

برای مشاهده کامل مطلب کلیک کنید