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

از طرفی دیگر استفاده از ایندکس های زیاد سبب ایجاد سربار در عملیات DML می شود و فضای دیتابیس را هدر می رود. در این متن یک QUERY برای یافتن ایندکس هایی که ستون های تکراری دارند معرفی می شود.

جدول testtbl دارای ستون های a ، b ، c ، d و e می باشد. فرض کنید ایندکس های زیر تعریف می شوند:

CREATE INDEX ind_1 ON testtbl(a,b,c,d);

CREATE INDEX ind_2 ON testtbl(a,b);

CREATE INDEX ind_3 ON testtbl (a,d,c);

CREATE INDEX ind_4  ON testtbl (b,d,a,c);

CREATE INDEX ind_5  ON testtbl (b);

CREATE INDEX ind_6  ON testtbl (a);

CREATE INDEX ind_7  ON testtbl (e);

در نگارش 12C و بالاتر می توان ایندکس های ind_8 و ind_9 را به صورت invisible ایجاد نمود:

CREATE UNIQUE INDEX ind_8  ON testtbl (a) invisible;

CREATE BITMAP INDEX ind_9  ON testtbl (e) invisible;

  

در این مثال با توجه به QUERYهای اجرایی در دیتابیس می توان ایندکس های ind_1 ، ind_4 ، ind_6 ، ind_7 و ind_8 را حذف کرد و ind_9 را visible نمود.

یک اشتباه رایج توسط برنامه نویسان دیتابیس ساخت ایندکس برای تمام ستون هایی است که در قسمت WHERE دستورات SQL قرار دارند از طرفی دیگر ساختن ایندکس های ترکیبی می تواند باعث ایجاد ایندکس های بی استفاده شود که تاثیر خاصی در افزایش سرعت اجرای دستورات SQL ندارند و باعث سربار در عملیات DML و هدر رفتن فضای دیتابیس می شوند. لازم به ذکر است OPTIMIZER فقط در شرایط خاص از ایندکس استفاده می کند.

نکته: برای شناسایی ایندکس های نامناسب می توان موارد زیر را درنظر گرفت:

-استفاده از گزارش AWR یا بعضی از QUERYها برای مشاهده تعداد موارد بکارگیری ایندکس ها توسط OPTIMIZER

-سایز ایندکس

–CLUSTERING FACTOR

-شناسایی QUERYهای مهم دیتابیس و بررسی استفاده آنها از ایندکس توسط explain plan یا execution plan

-شناسایی ایندکس های دارای ستون های یکسان

 

در ادامه یک QUERY برای شناسایی ایندکس هایی که در آنها یک یا چند ستون یکسان قرار دارند را مشاهده می کنید.

نکته: در این QUERY ایندکس های پارتیشن شده ( GLOBAL یا LOCAL ) در نظر گرفته نمی شوند.

نکته: در این QUERY ایندکس هایی که در دو اسکیمای ahmad و milad ساخته شده اند بررسی می شوند.

select

   a.table_owner,

   a.table_name,

   a.index_owner,

   a.index_name,

   column_name_list,

   column_name_list_dup,

   dup duplicate_indexes,

   i.uniqueness,

   i.partitioned,

   i.leaf_blocks,

   i.distinct_keys,

   i.num_rows,

   i.clustering_factor

from

  (

   select

      table_owner,

      table_name,

      index_owner,

      index_name,

      column_name_list_dup,

      dup,

      max(dup) OVER

       (partition by table_owner, table_name, index_name) dup_mx

   from

      (

       select

          table_owner,

          table_name,

          index_owner,

          index_name,

          substr(SYS_CONNECT_BY_PATH(column_name, ‘,’),2) 

          column_name_list_dup,

          dup

       from

          (

          select

            index_owner,

            index_name,

            table_owner,

            table_name,

            column_name,

            count(1) OVER

             (partition by

                 index_owner,

                 index_name) cnt,

             ROW_NUMBER () OVER

               (partition by

                  index_owner,

                  index_name

                order by column_position) as seq,

             count(1) OVER

               (partition by

                  table_owner,

                  table_name,

                  column_name,

                  column_position) as dup

   from

      sys.dba_ind_columns

   where

      index_owner not in (‘SYS’, ‘SYSTEM’))

where

   dup!=1

start with seq=1

connect by prior seq+1=seq

and prior index_owner=index_owner

and prior index_name=index_name

)) a,

(

select

   table_owner,

   table_name,

   index_owner,

   index_name,

   substr(SYS_CONNECT_BY_PATH(column_name, ‘,’),2) column_name_list

from

(

select index_owner, index_name, table_owner, table_name, column_name,

count(1) OVER ( partition by index_owner, index_name) cnt,

ROW_NUMBER () OVER ( partition by index_owner, index_name order by column_position) as seq

from sys.dba_ind_columns

where index_owner in (‘AHMAD’, ‘MILAD’))

where seq=cnt

start with seq=1

connect by prior seq+1=seq

and prior index_owner=index_owner

and prior index_name=index_name

) b, dba_indexes i

where

    a.dup=a.dup_mx

and a.index_owner=b.index_owner

and a.index_name=b.index_name

and a.index_owner=i.owner

and a.index_name=i.index_name

order by

   a.table_owner, a.table_name, column_name_list_dup;