آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

INDEX (اندیس ها) و کاربرد آن ها در پایگاه داده

INDEX(اندیس ها) و کاربرد آن ها در پایگاه داده

اندیس ها به پایگاه داده این امکان را می دهند که داده ها را بدون اینکه تمام جدول را بخوانند با سرعت بیشتری پیدا و بازیابی کنند. به عبارتی دیگر اندیس ها سرعت query گرفتن (پرس و جو ها) و جستجو برای اطلاعات در جدول را افزایش می دهند و فرایند پیدا کردن داده ها را بهینه سازی کند. از این رو توصیه می شود، برای ستون هایی که به طور متداول در عبارات WHERE بکار می روند، اندیس تعریف کنید. اندیس ها زمانی بسیار موثر واقع می شوند، که عملیات تنها بخش جزئی از داده را بازیابی کنند، برای مثال تنها 15% از کل داده های یک جدول را باز گرداند.
در ایجاد اندیس، لازم است دستور العمل ها و نکات زیر را رعایت کنید.


  • از ایجاد اندیس بر روی جداول کوچک خودداری کنید(جداولی که تعداد سطرهای آن محدود است).
  • از تعریف اندیس برای ستون هایی که دارای چندین مقدار NULL هستند، اجتناب کنید.
  • از تعریف اندیس BTree بر روی ستون هایی که دربردانده ی مقادیر تکراری زیادی هستند، خودداری کنید. در چنین موردی استفاده از اندیس BITMAP توصیه می شود.
  • سعی کنید تا حد امکان تعداد اندیس های یک جدول را محدود نگهدارید. اگرچه اندیس ها در مجموع سرعت جستجو و بازیابی داده را در یک جدول افزایش می دهند، در عین حال سرعت اجرای عملیات DML را کاهش خواهند داد، زیرا که اندیس های تعریف شده نیز باید همراه با بروز رسانی جدول حین اجرای عملیات UPDATE، INSERT و DELETE، بروز رسانی شود.

نحوه ی تعریف یا ایجاد INDEX

برای ایجاد اندیس، کافی است دستور create index را بکار ببرید.
دستور زیر یک اندیس بر روی ستون empno جدول emp ایجاد می کند.


create index empno_ind on emp (empno);‎

چنانچه مجبور به استفاده از دو ستون به طور مکرر در شرط های WHERE هستید، در این صورت تعریف یک اندیس ترکیبی (composite index) بر روی ستون یا ستون های مد نظر می تواند کمک شایانی به شما بکند. EMPNO و DEPTNO ستون هایی هستند که نیاز داریم به طور مکرر از آن ها همراه با هم در شرط های WHERE استفاده کنیم. این کار مستلزم ایجاد یک اندیس ترکیبی بر روی دو ستون مزبور است.


create index empdept_ind on emp (empno,deptno);‎

اندیس های BITMAP

این نوع اندیس بر روی ستون هایی ایجاد می شود که دربردارنده ی مقادیر تکراری متعدد بوده و نیز جدول دربردانده ی آن ها دارای سطرهای فراوانی (جدول بزرگ) می باشد. ستون City متعلق به جدول EMP از آن جایی که دربردانده ی مقادیر تکراری متعددی است، می تواند گزینه ی مناسبی برای ایجاد اندیس های BIMAP باشد. جهت ایجاد اندیس BITMAP کافی است دستور زیر را تایپ کنید.


create bitmap index city_ind on emp (city);

function based index

اندیس function-based مقدار یک عبارت که شامل یک یا چند ستون می باشد را محاسبه کرده، سپس آن را در اندیس ذخیره می کند. اندیس function-based کارایی query هایی که از index expression بهره می گیرند را افزایش می دهد. اندیس های function-based بجای اینکه بر اساس مقادیر ستون باشند، مبتنی بر عبارات (expression) هستند. برای مثال، اگر بکرات از عبارت SAL+COMM در شطرهای WHERE استفاده می کنید، در چنین موردی لازم است یک اندیس function-based بر روی عبارت مورد نظر، همانند نمونه ی زیر ایجاد کنید.


create index salcomm_ind on emp (sal+comm);‎

حال هر بار که عبارت SAL+COMM را در شرط where بکار می برید، oracle اندیس SALCOMM_IND را بکار می برد.


نحوه ی حذف اندیس

برای حذف یک اندیس از دستور DROP INDEX کمک می گیریم. مثال زیر اندیس SALCOMM_IND را حذف می کند:


drop index salcomm_ind;‎

بازیابی اطلاعات اندیس ها

به منظور مشاهده ی اطلاعات مربوط به اندیس ها و تعداد آن ها در schema خود، دستور زیر را تایپ کنید:


select * from user_indexes;‎

خوشه ها (cluster)

در مواردی که دو یا چند جدول را در یک ستون واحد با هم ادغام می کنید و همچنین بر روی آن به طور مکرر join query اجرا می کند، ایجاد یک گروه یا خوشه از جدول ها اکیدا توصیه می شود. Cluster یا خوشه عبارت است از مجموعه ای از جداول که دارای data block (گروه داده) های مشترک هستند؛ کلیه ی جداول به صورت فیزیکی در یک جا با هم ذخیره می شوند. به عنوان مثال می توان به جداول EMP و DEPT اشاره کرد که داخل ستون DEPTNO با هم پیوند داده شده اند. اگر آن ها را گروه بندی (cluster) کنید، Oracle به طور فیزیکی تمامی سطرها را به ازای هر دپارتمان از هر دو جدول EMP و DEPT در data block یکسان ذخیره می کند.


مزیت های cluster

از آنجایی که cluster سطرهای مربوطه از جدول های مختلف را در data block ای یکسان ذخیره می کند، ورودی/خروجی دیسک کاهش یافته و مدت زمان لازم برای دسترسی و بازیابی داده (access time) برای پیوند (join) جداول خوشه بندی شده (clustered table) افزایش می یابد. هر cluster key value تنها یکبار در هر cluster و cluster index ذخیره می شود، صرف نظر از اینکه این مقدار در چند سطر از جداول مختلف تکرار شده است. از این رو حافظه ی کمتری برای ذخیره سازی جداول مربوطه و index dataها نسبت به فرمتی که جدول ها در آن خوشه بندی نمی شوند، اشغال می گردد.


ایجاد cluster

به منظور ایجاد جداول خوشه بندی شده (clustered table)، اول یک cluster ایجاده می کنیم، در مرحله ی بعد اندیس را بر روی آن تعریف می کنیم و در نهایت جداول مورد نظر را در آن ایجاد می کنیم. برای مثال به منظور ایجاد یک خوشه یا cluster از جداول EMP و DEPT که در آن DEPTNO کلید cluster خواهد بود، ابتدا cluster را با تایپ دستور زیر ایجاد کنید.


create cluster emp_dept (deptno number(2));

در مرحله ی دوم اندیس را برای آن (بر روی cluster) ایجاد کنید.


create table dept (deptno number(2),‎


        ‎                     name varchar2(20),‎


        ‎                     loc varchar2(20))‎


        ‎                     cluster emp_dept (deptno);‎


        ‎ create table emp (empno number(5),‎


        ‎             name varchar2(20),‎


        ‎             sal number(10,2),‎


        ‎             deptno number(2)) cluster emp_dept (deptno)‎

حذف cluster ها

برای حذف cluster، کافی است دستور DROP CLUSTER را بکار ببرید. به عنوان نمونه، برای حذف خوشه ی emp_dept، دستور زیر را بکار ببرید.


drop cluster emp_dept;‎

دستور بالا در صورتی که cluster تهی باشد، آن را حذف می کند، بدین معنا که هیچ جدولی در cluster مورد نظر وجود نداشته باشد. در صورت وجود جدول در cluster، لازم است اول جداول درون آن را حذف کنید، سپس خود cluster را به وسیله ی دستور نام برده حذف کنید. حال به منظور حذف آن دسته از cluster هایی که دربردارنده ی جدول هستند، کافی است دستور زیر را اجرا کنید.


drop cluster emp_dept including tables;‎

بازیابی اطلاعات مربوط به cluster ها

برای استخراج و مشاهده ی اطلاعاتی درباره ی cluster ها و تعداد آن در schema، دستور زیر را تایپ کنید.


select * from user_clusters;‎

برای اینکه بفهیم چه تعداد از جداول موجود جزئی از یک cluster هستند، دستور زیر را بکار می بریم.


select * from tab


        TABLE_NAME    TYPE      CLUSTER_ID


        ‎----------    ----      -----------‎


        EMP           TABLE         1‎


        SALGRADE      TABLE         ‎


        CUSTOMER      TABLE         ‎


        DEPT          TABLE         1‎
 

در مثال بالا، به ستون CLUSTER_ID توجه کنید، CLUSTER_ID دو جدول EMP و DEPT، 1 می باشد که نشان دهنده ی وجود این جداول در cluster ای است که شناسه ی آن 1 می باشد. می توان اسم cluster-id را در جدول USER_CLUSTERS مشاهده کرد.


  • 2495
  •    966
  • تاریخ ارسال :   1394/07/27

دانلود PDF دانشجویان گرامی اگر این مطلب برای شما مفید بود لطفا ما را در GooglePlus محبوب کنید
رمز عبور: tahlildadeh.com یا www.tahlildadeh.com
ارسال دیدگاه نظرات کاربران
شماره موبایل دیدگاه
عنوان پست الکترونیک

ارسال

آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

تمامی حقوق این سایت متعلق به آموزشگاه تحلیل داده می باشد .