همانطور که در مطلب “آشنایی با Clustering Factor در اوراکل” بیان شد، برای محاسبه مقدار  Clustering Factor، بررسی می شود که آیا Data Block ارجاع داده شده برای دو  index entry  پشت سرهم، با هم متفاوت هستند یا خیر؟ در صورت یکسان بودن Data Blockها، Clustering Factor  در همان مقدار قبلی اش باقی خواهد ماند و در صورت تفاوت، یک عدد به مقدار Clustering Factor اضافه می شود به عبارتی، با هر سوییچی بین Data Blockها، یک عدد به Clustering Factor افزوده خواهد شد.

با این بیان، در زمان محاسبه clustering factor توسط بسته dbms_stats اگر index entry جدید به data blockای اشاره کند که اخیرا(غیر از data block آخر) مورد دستیابی قرار گرفته باشد و حتی در حافظه هم موجود باشد، باز هم مقدار clustering factor ایندکس افزایش پیدا خواهد کرد.

create table tb(id number,session_number varchar2(1000),dummy varchar2(1000),temp varchar2(1000));

create index tb_id_ind1 on tb(id);

create sequence sq1 order nocache;

از طریق سه session به صورت همزمان، در این جدول اطلاعاتی را ثبت می کنیم:

–session 1

SQL> insert into tb select sq1.nextval,’session 1′,’tehran   2’||level,’iran   3’||rownum||” from dual connect by level<=100000;

–session 2

SQL>  insert into tb select sq1.nextval,’session 2′,’tehran   2’||level,’iran   3’||rownum||” from dual connect by level<=100000;

–session 3:

SQL>  insert into tb select sq1.nextval,’session 3′,’tehran   2’||level,’iran   3’||rownum||” from dual connect by level<=100000;

در این شرایط، ایندکس tb_id_ind1ء، CLUSTERING FACTOR خوبی ندارد:

SQL> exec dbms_stats.gather_table_stats(ownname => user,tabname => ‘TB’,cascade => true);

PL/SQL procedure successfully completed

SQL> SELECT t.blocks, t.num_rows, i.clustering_factor FROM dba_tables t, dba_indexes i WHERE t.table_name = i.table_name AND i.index_name=’TB_ID_IND1′;

    BLOCKS   NUM_ROWS CLUSTERING_FACTOR

———- ———- —————–

      2062     300000            260191

با این اوصاف، برای اجرای پرس و جوی زیر، نیاز به انجام FULL TABLE SCAN خواهد بود:

select * from tb where id between 3000 and 4000;

توجه: رکوردهای 3000 تا 4000 در 8 بلاک قرار گرفته اند در صورتی که جدول 2062 بلاک دارد:

select distinct  dbms_rowid.rowid_block_number(rowid) as block# from tb where id between 3000 and 4000;

39683

39698

39685

39686

39684

39697

39687

39696

در اوراکل 12cء، preference جدیدی به نام table_cached_blocks اضافه شده که می تواند بهنگام محاسبه مقدار CLUSTERING_FACTOR، بلاکهایی که اخیرا توسط index entryها ارجاع داده شده اند را هم لحاظ کند با این امید که شاید این Data Blockها هنوز در حافظه باقی مانده باشد.

این preference در سطح table قابل تنظیم است(همچنین در سطح دیتابیس) و مقدار آن می تواند عددی بین 1 تا 255 باشد این عدد بیانگر تعداد Data Blockهایی می باشد که اخیرا توسط index entryهای قبلی ایندکس ارجاع داده شده و احتمالا در حافظه هم موجود هستند.

برای مثال تنظیم مقدار 3 برای این پارامتر، به این معنی است که اگر در حین محاسبه CLUSTERING_FACTORء، Data Block ارجاع داده شده توسط index entry جدید، توسط یکی از سه index entry قبلی ارجاع داده شده بود، مقدار CLUSTERING_FACTOR افزایش پیدا نخواهد کرد.

توجه: مقدار پیش فرض این preference، برابر با null می باشد.

برای تنظیم این preference می توان از پروسجر set_table_prefs در بسته dbms_stats استفاده کرد:

SQL> execute dbms_stats.set_table_prefs(null,’TB’,’table_cached_blocks’,3);

PL/SQL procedure successfully completed

برای محاسبه مقدار جدید clustering factor، مجددا از این جدول(و یا ایندکس) ، آمار جمع آوری می کنیم:

SQL> exec dbms_stats.delete_index_stats(ownname => user,indname => ‘TB_ID_IND1’);

PL/SQL procedure successfully completed

با اجرای پرس و جوی زیر خواهیم دید که CLUSTERING_FACTOR ایندکس از 260191 به 2279 تغییر کرده است:

SQL> SELECT t.blocks, t.num_rows, i.clustering_factor FROM dba_tables t, dba_indexes I WHERE t.table_name = i.table_name AND i.index_name=’TB_ID_IND1’;

    BLOCKS   NUM_ROWS CLUSTERING_FACTOR

———- ———- —————–

      2747     300000              2279

بعد از انجام این تغییر، مجددا execution plan دستور زیر را بازبینی می کنیم:

select * from tb where id between 3000 and 4000;

نکات پایانی

نکته 1: در زمان بازسازی و یا ایجاد ایندکس، خواهیم دید که اوراکل در زمان محاسبه CLUSTERING_FACTOR از مقدار table_cached_blocks صرف نظر خواهد کرد:

SQL> alter index TB_ID_IND1 rebuild;

Index altered

SQL> 

    BLOCKS   NUM_ROWS CLUSTERING_FACTOR

———- ———- —————–

      2747     300000            260191

نکته 2: برای مشاهده مقدار جاری یک preference می توان از تابع dbms_stats.get_prefs استفاده کرد:

select dbms_stats.get_prefs(pname=>’TABLE_CACHED_BLOCKS’,ownname=>’USEF’,tabname=>’TB’) preference from dual;

نکته 3: preference مذکور را می توان در سطح دیتابیس هم تنظیم کرد:

SQL> exec dbms_stats.SET_GLOBAL_PREFS(pname => ‘TABLE_CACHED_BLOCKS’,pvalue => ‘3’);

PL/SQL procedure successfully completed

نکته 4: پیشنهاد جناب Jonathan Lewis تنظیم مقدار این preference به عدد 16 می باشد.