Oracle 12c ile birlikte partition tablolarda “indexing off” ve “indexing partial” clause ile indexleri sadece istemiş olduğumuz partitionlar üzerine create edebiliriz. Oracle 12c öncesinde partition tablolarda index create ederken local veya global olmak üzere 2 farklı şekilde index create edebiliyorduk. Artık partial index create edebilir durumdayız. İndex create ederken indexing off opsiyonu ile index create etmek demek ilgili partition üzerinde index create etmemek anlamına gelmektedir.
Sadece ilgilendiğimiz partitiona index create etmek opsiyonunu ne işimize yarar diye düşündüğümüz de ilk aklıma gelen nedeni, çok büyük tabloları partitionladığınız da sadece üzerinde çalışacağınız partitionlara index create ederek zamandan ciddi tasarruf edebilirsiniz.
Her zamanki gibi bir örnekle durumu açıklamaya çalışalım.
Bir partition tablo create edelim;
===> CREATE TABLE kamil.test_range
(id NUMBER(10),
name varchar2(10),
create_date date)
PARTITION BY RANGE(create_date)
INTERVAL( NUMTODSINTERVAL(1,’DAY’))
(PARTITION P1 VALUES LESS THAN (TO_DATE(’01-01-2000′, ‘DD-MM-YYYY’)))
Table created.
Şimdi test tablomuza sample data insert edelimki üzerinde çalışmak için partitionlarımız oluşabilsin;
===> insert into kamil.test_range values (1,’t1′,’01/01/1999′)
1 row created.
===> insert into kamil.test_range values (1,’t1′,’01/01/2000′)
1 row created.
===> insert into kamil.test_range values (2,’t2′,’01/03/2001′)
1 row created.
===> insert into kamil.test_range values (3,’t3′,’01/04/2002′)
1 row created.
===> commit
Commit complete.
Şimdi hangi partitionlarımız var ona bakalım;
===> col table_name format A20
===> col PARTITION_NAME format A15
===> SELECT
table_owner||’.’||table_name table_name,
partition_name
FROM dba_tab_partitions
where table_owner = ‘KAMIL’
TABLE_NAME PARTITION_NAME
——————– —————
KAMIL.TEST_RANGE SYS_P1431
KAMIL.TEST_RANGE SYS_P1432
KAMIL.TEST_RANGE SYS_P1433
KAMIL.TEST_RANGE P1
4 rows selected.
Yapmış olduğumuz insertler sonrasında partition tablomuzda 4 adet partition create oldu. Şimdi bazı partitionlar üzerine index create edelim dolayısıyla diğerlerinde de index create olmadığını görelim.
Önce index create etmeyeceğimiz partitionları alter ederek “indexing off” olarak işaretliyoruz.
===> alter table KAMIL.TEST_RANGE modify partition SYS_P1433 indexing off
Table altered.
===> alter table KAMIL.TEST_RANGE modify partition SYS_P1432 indexing off
Table altered.
Sys_p1433 ve p1 partitionlarında index create edelim;
===> create index kamil.indx_1 on KAMIL.TEST_RANGE(create_date) local indexing partial
Index created.
Şimdi hangi partitionlarda index var hangilerinde yok kontrol edelim.
===> col index_name format a10
===> col PARTITION_NAME format a14
===> select index_name, partition_name, segment_created
from dba_ind_partitions where index_owner = ‘KAMIL’
INDEX_NAME PARTITION_NAME SEGMENT_CREATED
———- ————– —————
INDX_1 P1 YES
INDX_1 SYS_P1434 YES
INDX_1 SYS_P1435 NO
INDX_1 SYS_P1436 NO
4 rows selected.
Ek olarak,
===> col table_name format a10
===> col partition_name format a15
===> select table_name, partition_name, indexing from dba_tab_partitions where table_name = ‘TEST_RANGE’
TABLE_NAME PARTITION_NAME INDEXING
———- ————— ——–
TEST_RANGE SYS_P1439 OFF
TEST_RANGE SYS_P1438 OFF
TEST_RANGE SYS_P1437 ON
TEST_RANGE P1 ON
4 rows selected.
Partition tablolarda partition bazında index opsiyonunun hangilerin de kapalı veya açık olduğunu select etmek içinde yukarıdaki scripti kullanabilirsiniz.
Partitionları “Indexing_off” opsiyonunu ile alter etmek demek o partition üzerine hiçbir şekilde index create edilmeyeceği anlamına gelmez. “Partial indexing” opsiyonu ile index create etmek istendiğinde sadece indexing_off yapılmış olan partitionlar üzerine index create edilmeyeceği anlamına gelir. Örneğin;
create index kamil.indx_2 on KAMIL.TEST_RANGE(name) local ;
Index created.
Yukarıdaki şekilde index create ettiğimiz de toplam 4 tane olan partitionlarımız dan 2 tanesinin indexing_off olarak alter edilmesine rağmen;
===> col index_name format a10
===> col PARTITION_NAME format a14
===> select index_name, partition_name, segment_created
from dba_ind_partitions where index_owner = ‘KAMIL’ and index_name =’INDX_2′
INDEX_NAME PARTITION_NAME SEGMENT_CREATED
———- ————– —————
INDX_2 P1 YES
INDX_2 SYS_P1443 YES
INDX_2 SYS_P1444 YES
INDX_2 SYS_P1445 YES
4 rows selected.
Tüm partitionlar üzerine index create edilmiş olacağını görürsünüz.
Eğer partitionlı olarak oluşturduğunuz çok büyük tablolarınız varsa ve bu tablolardan eski dataları silemiyorsanız uygulama tarafından da bu datalara erişim olmadığını biliyorsanız bu opsiyonu rahatça kullabilirsiniz. Gereksiz index create etme işleminden kaçınmak demek aynı zaman da size disk üzerinde yer kazandıracaktır.
Yukarıdaki örnekde daha önceden create edilmiş olan bir partition tablonun üzerindeki partitionlarda sonrasında nasıl işlem yapabileceğimizi görmüş olduk. Partition tabloyu create ederken eğer partitionlarınızı biliyorsanız ilk create aşamasında da hangi partitionda index olsun hangisinde olmasın tanımlamasını da orda yapabilirsiniz. Örneğin;
===> CREATE TABLE kamil.test_range2
(id NUMBER(10),
name varchar2(10),
create_date date)
PARTITION BY RANGE(create_date)
(PARTITION P1 VALUES LESS THAN (TO_DATE(’01-01-2000′, ‘DD-MM-YYYY’)) INDEXING OFF,
PARTITION P2 VALUES LESS THAN (TO_DATE(’01-01-2001′, ‘DD-MM-YYYY’)) INDEXING OFF,
PARTITION P3 VALUES LESS THAN (TO_DATE(’01-01-2002′, ‘DD-MM-YYYY’)) INDEXING ON,
PARTITION P4 VALUES LESS THAN (TO_DATE(’01-01-2003′, ‘DD-MM-YYYY’)) INDEXING ON,
PARTITION P5 VALUES LESS THAN (TO_DATE(’01-01-2004′, ‘DD-MM-YYYY’))
)
Table created.
Bu şekilde de index create edilmemesini istediğimiz partitionları set edebiliriz. P5 partitionın sonuna özellikle indexing on veya off clause’ unu eklemedim. Nedeni ;
===> col table_name format a10
===> col partition_name format a15
===> select table_name, partition_name, indexing
from dba_tab_partitions where table_name = ‘TEST_RANGE2’
TABLE_NAME PARTITION_NAME INDEXING
———- ————— ——–
TEST_RANGE P5 ON
TEST_RANGE P4 ON
TEST_RANGE P3 ON
TEST_RANGE P2 OFF
TEST_RANGE P1 OFF
5 rows selected.
Default’ unun ON olduğunu göstermek içindi.
Database’ de hang index full hangi index partial olarak create edildiğini select etmek için;
===> col index_name format a10
===> col indexing format a8
===> SELECT index_name,
indexing
FROM dba_indexes where index_name in(‘INDX_1′,’INDX_2’)
ORDER BY 1
INDEX_NAME INDEXING
———- ——–
INDX_1 PARTIAL
INDX_2 FULL
2 rows selected.
Partial indexler ile ilgili olarak nedir, nasıl ve nerelerde kullanılır sorularına cevap vermiş olduk.
