در نسخه های اوراکل پیش از 12c، ایجاد ایندکس برای یک جدول پارتیشن بندی شده، سبب ایجاد ایندکس برای همه پارتیشنهای آن جدول می شد. چالش اساسی در این زمینه زمانی مطرح می شود که در مواردی، ایجاد ایندکس، اساسا کاربردی برای پارتیشنهای قدیمی جدول و یا حداقل بعضی از پارتیشنهای آن، ندارد.

البته به عنوان یک ترفند تقریبا ناقض، در اوراکل نسخه 11g، می توان ایندکسهای از نوع LOCAL را از ابتدا به صورت UNUSABLE ایجاد کرد و پس از آن، ایندکس را برای پارتیشنهای مورد نظر، بازسازی نمود:

SQL> CREATE INDEX idx_11g ON mytbl_11g(birth_date) LOCAL UNUSABLE;

Index created

select index_name, partition_name, status from dba_ind_partitions  where index_name=’IDX_11G’;

SQL> ALTER INDEX idx_11g REBUILD PARTITION p3;

Index altered

select index_name, partition_name, status from dba_ind_partitions  where index_name=’IDX_11G’;

در نسخه 12c، اوراکل قابلیت جدیدی را با عنوان Partial Indexing ارائه کرد که با کمک آن می توان ایجاد ایندکس را در سطح پارتیشن فعال و یا غیرفعال نمود البته این موضوع صرفا برای ایندکسهایی که با عبارت INDEXING PARTIAL ایجاد می شوند، صدق خواهد کرد.

این ویژگی سبب صرفه جویی در فضای مصرفی، بهبود سرعت ساخت و نگهداری ایندکسها و همچنین افزایش سرعت دستورات DMLای بر روی جداول پارتیشن بندی شده خواهد شد.

برای استفاده از این قابلیت می توان در زمان اجرای دستور create table، از عبارت INDEXING  بهره گرفت به این صورت که، استفاده از عبارت INDEXING OFF برای یک پارتیشن، مانع از ایجاد ایندکس بر روی آن پارتیشن خواهد شد(البته صرفا برایpartial indexها).

همچنین می توان INDEXING را در سطح جدول فعال و یا غیرفعال نمود که در این صورت، این تصمیم برای پارتیشنهای جدید هم اعمال خواهد شد.

نکته: مقدار پیش فرض خصیصه INDEXING در سطح جدول و پارتیشن، برابر با on می باشد.

در دستور زیر مشاهده می کنید که جدول mytbl، با سه پارتیشن p2، p1 و p3 ایجاد می شود و ایجاد یک PARTIAL INDEX، سبب ایجاد ایندکس برای پارتیشنهای p2، p1 و پارتیشنهای جدید، نخواهد شد:

SQL> create table usef.mytbl(id number(9), birth_date date,DES_ VARCHAR2(4000)) INDEXING OFF

    PARTITION BY RANGE (birth_date)

    INTERVAL(NUMTOYMINTERVAL(12, ‘MONTH’))

    (

    partition p1 VALUES LESS THAN(TO_DATE(’02/01/1360 00:00:00′,’MM/DD/YYYY HH24:MI:SS’)) INDEXING OFF,

    partition p2 VALUES LESS THAN(TO_DATE(’02/01/1370 00:00:00′,’MM/DD/YYYY HH24:MI:SS’)) INDEXING OFF,

    partition p3 VALUES LESS THAN(TO_DATE(’02/01/1380 00:00:00′,’MM/DD/YYYY HH24:MI:SS’)) INDEXING ON

    );

 Table created

بعد از ساخت جدول هم می توان با کمک دستور alter table خصیصه INDEXING را در سطح پارتیشن، فعال یا غیرفعال کرد:

SQL> alter table usef.mytbl modify partition p2 indexing on;

 Table altered

SQL> alter table usef.mytbl modify partition p2 indexing off;

 Table altered

در ادامه قصد داریم با درج اطلاعاتی در جدول mytbl، وضعیت ایندکسهای این جدول و همچنین پرس و جوهایی که بر روی آن اجرا می شوند را مورد بررسی قرار دهیم.

با اجرای بلاک pl/sqlای زیر، اطلاعاتی را به این جدول اضافه می کنیم:

SQL> begin

    for i in 1.. 5000000 loop

  insert into usef.mytbl values(1,TO_DATE(’01/01/1353 00:00:00′,’MM/DD/YYYY HH24:MI:SS’),RPAD(‘TEST’,20,’TETS’));

  insert into usef.mytbl values(i,TO_DATE(’04/19/1369 00:00:00′,’MM/DD/YYYY HH24:MI:SS’),RPAD(‘TEST’,20,’TETS’));

  insert into usef.mytbl values(i,TO_DATE(’09/25/1375 00:00:00′,’MM/DD/YYYY HH24:MI:SS’),RPAD(‘TEST’,20,’TETS’));

  insert into usef.mytbl values(i,TO_DATE(’05/17/1386 00:00:00′,’MM/DD/YYYY HH24:MI:SS’),RPAD(‘TEST’,20,’TETS’));

  insert into usef.mytbl values(i,TO_DATE(’07/02/1398 00:00:00′,’MM/DD/YYYY HH24:MI:SS’),RPAD(‘TEST’,4000,’TETS’));

    end loop;

    commit;

   end;

  /

 PL/SQL procedure successfully completed

همچنین آمار مربوط به این جدول را بروز رسانی می کنیم:

SQL> begin

      dbms_stats.gather_table_stats(ownname =>’USEF’ ,tabname =>’MYTBL’ ,degree => 5,cascade => true);

    end;

    /

 PL/SQL procedure successfully completed

با درج این اطلاعات، حجم هر کدام از پارتیشنهای این جدول به صورت زیر درخواهند آمد:

select p.segment_name,p.partition_name,p.bytes/1024/1024  SIZE_MB from dba_segments p where p.segment_name=’MYTBL’ order by 2;

همانطور که می بینید، دو پارتیشن جدید هم با نامهای SYS_P1443 و SYS_P1444 برای این جدول ایجاد شده اند.

با دستور زیر، فعال یا غیرفعال بودن خصیصه INDEXING را برای این پارتیشنها مشخص می کنیم:

select d.table_name,d.partition_name,d.indexing from dba_tab_partitions d where d.table_name=’MYTBL’;

می بینید که برای پارتیشنهای جدید، خصیصه INDEXING برابر با OFF تنظیم شده است همانطور که در ابتدای متن هم بیان کردیم، چرایی این مسئله به تعیین عبارت INDEXING OFF در زمان ساخت جدول برمی گردد:

SQL> create table usef.mytbl(id number(9), birth_date date) INDEXING OFF

…

برای مشاهده مقدار فعلی خصیصه INDEXING در سطح یک جدول، می توان به ستون def_indexing از ویوی user_part_tables مراجعه کرد:

select table_name, def_indexing from user_part_tables where table_name=’MYTBL’;

برای تغییر این مقدار، می توان از دستور زیر استفاده نمود:

SQL> alter table MYTBL modify default attributes indexing ON;

Table altered

قصد داریم ایندکسی از نوع local را بر روی ستون birth_date به دو صورت FULL INDEXING و PARTIAL INDEXING ایجاد کنیم همچنین بعد از ایجاد این ایندکس، زمان سپری شده و همچنین حجم مصرفی ایندکس را در هر دو حالت، مورد بررسی قرار خواهیم داد:

–PARTIAL INDEXING

SQL> startup force;

SQL>  set timing on

SQL> CREATE INDEX idx1 ON usef.MYTBL(birth_date) LOCAL INDEXING PARTIAL;

Index created.

Elapsed: 00:00:07.96

ایندکس در مدت زمان 8 ثانیه ایجاد شد!

دستور زیر نشان می دهد که ایندکس idx1، همانند جدول mytbl پارتیشن بندی شده است:

select index_name, partition_name, status from dba_ind_partitions  where index_name=’IDX1′ order by partition_position;

همانطور که در خروجی دستور بالا قابل مشاهده است، صرفا ایندکسی که بر روی پارتیشن P3 ایجاد شده است، در وضعیت USABLE قرار دارد! همچنین دستور زیر نشان می دهد که مطابق پیش بینی ها، صرفا یکی از این ایندکسها، فضایی را اشغال کرده است و مابقی حتی فاقد segment می باشند!(البته این مسئله به مقدار پارامتر deferred_segment_creation هم بستگی دارد):

select p.segment_name,p.partition_name,p.bytes/1024/1024 SIZE_MB from dba_segments p where p.segment_name=’IDX1′;

با اجرای پرس و جو بر روی پارتیشنهایی غیر از p3، انجام TABLE ACCESS FULL امر بسیار بدیهی و روشنی خواهد بود:

SQL>SELECT COUNT(birth_date) FROM USEF.MYTBL WHERE  birth_date BETWEEN TO_DATE(’02/01/1350′,’MM/DD/YYYY’) AND TO_DATE(’02/01/1359′,’MM/DD/YYYY’) ;

و ایندکس idx1، صرفا پارتیشن p3 را پوشش می دهد:

SQL>SELECT COUNT(birth_date) FROM USEF.MYTBL WHERE  birth_date BETWEEN TO_DATE(’02/01/1372′,’MM/DD/YYYY’) AND TO_DATE(’02/01/1379′,’MM/DD/YYYY’);

نکته 1: پارتیشنهای unusable را می توان به صورت دستی بازسازی کرد:

SQL> ALTER INDEX IDX1 REBUILD PARTITION p1;

Index altered

SQL> select index_name, partition_name, status from dba_ind_partitions  where index_name=’IDX1′ and partition_name=’P1′ order by partition_position;

نکته 2: با تغییر خصیصه INDEXING از ON به OFF، ایندکس مورد نظر، در وضیعت UNUSABLE قرار خواهد گرفت همچنین تغییر این مقدار از OFF به ON هم سبب USABLE شدن ایندکس خواهد شد.

قصد داریم همین سناریو را برای حالت FULL INDEXING انجام دهیم. به این جهت، ایندکس idx1 را حذف می کنیم:

SQL> drop INDEX  idx1;

 Index dropped

با دستور زیر، ایندکس را با عبارت INDEXING FULL ایجاد می کنیم:

–FULL INDEXING

SQL> startup force;

SQL> CREATE INDEX  idx1 ON usef.MYTBL(birth_date) LOCAL INDEXING FULL;

Index created.

Elapsed: 00:01:04.89

زمان سپری شده، بیش از یک دقیقه می باشد در صورتی که ساخت ایندکس به همراه عبارت INDEXING PARTIAL، تقریبا 8 ثانیه به طول انجامید.

دستور زیر نشان می دهد که ایندکس idx1، همانند جدول mytbl پارتیشن بندی شده است:

select p.index_name,p.partition_name from dba_ind_partitions p where p.index_name=’IDX1′;

ایجاد FULL INDEXING، سبب خواهد شد تا حجم بیشتری برای ایندکس idx1 در نظر گرفته شود:

select p.segment_name,p.partition_name,p.bytes/1024/1024 SIZE_MB from dba_segments p where p.segment_name=’IDX1′;