index چیست؟

با یک مثال index را توضیح می‌دهم، فرض کنید یک کتاب ۴ هزار صفحه‌ای را باز کرده‌اید، وقتی می‌خواهید به مطلبی رجوع کنید قطعاً شما تمام کتاب را نمی‌خوانید تا به مطلب مورد نظر برسید. در این حالت شما به فهرست رجوع می‌کنید و به صفحه مورد نظر می‌روید. دقیقاً تعریف index همان فهرست در کتاب است.

در حقیقت یعنی از روی اون شاخصی به صفحه موردنظر می‌رسید. 

اوراکل روش‌های مختلفی را برای indexing به کار می‌برد که در ادامه با آنها آشنا خواهیم شد.

در چه شرایطی index گذاری می‌کنیم؟

کوئری ما کمتر از ۵ درصد کل رکورد را بر گرداند.

زمانی index گذاری می‌کنیم که عموم کوئری‌هایی که از اون جدول می‌گیرید کمتر از ۵ درصد از کل رکوردها رو برگرداند. توی این شرایط ما index گذاری انجام می‌دهیم. 

یادتان باشد در شرایطی اگر اصلاً index گذاری نشود بهتر است.

مثال: جدولی حاوی ۱۰۰ رکورد است کوئری وارد شده برای گزارش‌گیری توسط ما ۵۰ رکورد آن را بیشتر برنمی‌گرداند، آیا index گذاری باید انجام شود؟

ج: خیر - چون با ایندکس‌گذاری روی همچین جدولی برای همچین کوئری‌هایی سرعت دیتابیس ما به شدت افت پیدا خواهد کرد.

چرا با index گذاری performance db پایین می‌آید؟

برای اینکه خود index زمانی را از دیتابیس ما برای ایجاد فهرست و رجوع به آن از ما تلف می‌کند.

مثل این می‌ماند که ۵۰ صفحه را بخواهید بخوانید ولی هربار برای خواندن هر صفحه یکبار به فهرست رجوع کنید.

س: اگر کوئری ما بر روی فیلدهای کلید خارجی باشد و خروجی بیش از ۵ درصد باشد ولی در هرحال ما  از چند فیلد کم حجم کوسری گرفته‌ایم، آیا index گذاری باید بهتر است انجام شود یا خیر؟

ج: خیر

س: فرض بگیرید ما ۲ جدول را بخواهیم باهم join کنیم و کوئری ما بیش از ۵ درصد کل رکوردها رو برمی‌گردونه آیا index گذاری روی کلید خارجی انجام بشود؟

ج: خیر - پیشنهاد نمی‌شود، در اصل یعنی هر جدول بیش از ۵ درصد را برمی‌گرداند (یکی ۱۰ درصد، یکی ۲۰ درصد) در این حالت اگر شما index گذاری کنید و کوئری بگیرید زمان بیشتری طول خواهد کشید تا نتایج به شما برگرداند. (اگر index را drop کنید خیلی بهتر است و کوئری شما سریعتر انجام می‌شود)

روش Btree

Btree یک درخت متوازن می‌باشد انتهای هر شاخه rowed قرار دارد اگر انتهای پرسو جو where خانه index گذاری شده مورد جستوجو قرار گیرد اوراکل ابتدا به انتهای شاخه نگاه کرده سپس تا سر شاخه آن را بازیابی می‌کند.

خوب است، مقادیر متمایز کننده ستون index زیاد است

یعنی زمانی شما باید از Index Btree استفاده کنید که می‌خواهید روی روی فیلدی همانند id ایندکس‌گذاری کنید.(مثل کد ملی، شناسنامه)

یعنی از این اطمینان دارید که این فیلد داده تکراری زیاد در خودش ندارد.

نکته: جنسیت، شهر و ... اصلاً فیلد خوبی برای ایندکس‌گذاری با Btree نیست. چون تکرار زیاد دارند.

نکته: همچنین سعی کنید فیلدی که به عنوان ایندکس تعیین می‌کنید توی دستورات where ازش استفاده بشود. تا زمانی که تو دستور where از index استفاده نکنی index تاثیرگذار نیست. (یادتان باشد فقط با Index گذاری تنها زمان اجرای کوئری‌های شما پایین نمی‌آید.) 

هنگامی که در دستور where خانه مورد نظر index است:

::SAMPLE-1::
SQL> Create index aaaa on t1(id) tablespace tsp;
::SAMPLE-2::
SQL> CREATE TABLE T2(ID NUMBER, NAME VARCHAR2(20), NC NUMBER);
Table created.

SQL> CREATE INDEX SIB ON T2(ID, NC);
Index created.

نکته: اسم index زباد مهم نیست.

نکته: در مثال دوم اولویت اول با ID و اولویت دوم با (NC(NATIONAL CODE برای INDEX گذاری است.

نکته: یادتان باشد هیچوقت NAME رو تو اولویتتون برای INDEX گذاری نذارید.

نکاتی برای Optimizing Index

  • سعی کنید شاخص اولیه index اتون عددی کوچیک باشه، مثلاً NC برای شاخص اول بودن بزرگه و ID از اون بهتره
  • حتماً شاخص اولتون بدون تکرار باشه، شاخص ثانویه طولانی باشه مشکلی نداره ولی ترجیحاً بازهم unieq باشه(تکرار نداشته باشه)

س: به صورت پیش‌فرض primary key به صورت index شده است؟

بله - حتی rowid هم ایندکس شده است ولی به محض اینکه شما index ای ایجاد می‌کنید index های اوراکل از کار می‌افتد. چون ۱ جدول نمی‌تنه دارای ۲ آبجکت index باشه (اول id بعد rowid اگه id نداشته باشه اول rowid میشه)

نکته: ROWID = Objectid+Datafileid+Datablock+Rownumber که این رشته در کل دیتابیس برای هر رکورد unique است

س: توی sqlserver ما یک clustered index داریم و یک nonclustered index خب clustered index تو هر جدول یکی بیشتر نمی‌تونه باشه(مثلاً primary key) توی nonclustered index ما می‌تونیم فیلدهای زیادی رو داشته باشیم ولی اگه تعداد رکوردهاش زیاد بشه چون ایندکس یک pointers است سرعت کم می‌شود، در اوراکل index گذاری به چه صورت است؟

ج: در اوراکل هر جدول می‌تواند چندین آبجکت index داشته باشد هر index هم می‌تونه روی چندتا فیلد باشد و به ترتیب است. همچنین دقت کنید به هیچ عنوان تمام فیلدهای جدول نباید در یک index قرار بگیرند.(حداکثر ۲ یا ۴ فیلد به نظر من) در sqlserver‌ هم قابلیت گذاشتن چند آبجکت Index وجود دارد و در حقیقت sqlserver همه را یک آبجکت index به طور کلی در نظر می‌گیرد.

روش Bitmap

زمانی که تعداد رکوردهایی که توی result امون داریم زیاده(بالای ۵ درصد)  و فیلدی که تو where‌ استفاده می‌کنیم اون فیلد تکرار زیاد داره(البته خیلی پسندیده نیست تکرار زیاد داشته باشه) مثل (city) شما بهتره از index Btree استفاده نکنید و از index bitmap استفاده کنید. index bitmap اطلاعات رو توی RAM طبقه‌بندی می‌کنه

نکته: این index به صورت snapshot بیس عمل می‌کند.

نکته: اگر می‌خواهید بر روی id, name ایندکس بگذارید از bitmap استفاده کنید چون name تکرارش زیاده و اگر می‌خواهید بر روی id, nc ایندکس بگذارید از btree استفاده کنید.

این index در کویری‌هایی که بسیار خروجی زیادی دارند استفاده می شود مخصوص data ware houseها

توجه کنید Data warehouse حتما index داشته باشند data warehouse به بانک‌های اطلاعاتی که بیشتر از آنها اطلاعات خوانده می شود گویند به بانک‌های اطلاعاتی معمولی (oltp (Online transaction processing گفته می‌شود

برای ساخت index bitmap به مثال زیر توجه کنید:

Create bitmap index aaa on t1(id,name) tablespace tsp;

نکته: تاکید می‌کنم یک فیلد جدول در اوراکل نمی‌تواند دارای ۲ ایندکس باشد. روی هر فیلدی فقط آخرین index می‌مونه و تمام Indexهای قبلی بلا استفاده می‌ماند.

نکته: در فضای SGA ما نمی‌توانیم از یک جدول ۲ مدل کش داشته باشیم واسه همین همیشه آخرین 

نکته: اگر وقتی دیتابیس بالا است index بگذاریم دیتامون دوباره تو فضای SGA کش می‌شود و با اولین select چون نوع index عوض شده با index جدید کوئری را انجام می‌دهد.

نکته: هیچوقت قبل از اینکه کوئری بگیرید index را drop نکنید تا یک index گذاری جدید انجام دهید. چون با این کار شما فضای کش را از بین می‌برید و یک فضای کش جدید ایجاد می‌کنید(کش را flush می‌کنید) (هر جایی index تغییر کرد باید دوباره داده‌ها کش بشه حتی اگر قبلاً index را گذاشته بوده باشیم و کش شده باشد)

نکته: در index پیش‌فرض اوراکل به صورت پیش‌فرض اول primary key در نظر گرفته میشه و بعد rowid اگر primary key جدولمون نداشته باشه فقط rowid در نظر گرفته میشه و index میشه

دیتا دیکشنری indexها

دیتا دیکشنری indexها اسم جدول رو میده، اسم index رو میده، حتی history index رو هم میده

Dba_ind_columns
Dba_indexes