ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

Mevcut hata partition bir tabloya local / partition bir index yaratılmak istenirken alınan hatadır.

create unique index USTA.MUHASEBE_IX01 on USTA.TABLE_MUHASEBE (log_guid)
  tablespace LARGE_TBS_01 local;

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

Partition index non-Unique olmak zorundadır. Doğru şekli:

create index USTA.MUHASEBE_IX01 on USTA.TABLE_MUHASEBE (log_guid)
  tablespace LARGE_TBS_01 local;

 

Dolu tablonun partitioning yapılması

   Merhaba , Table Partitioning yapısını daha önce anlatmıştım. Peki ya Dolu olan tablomu nasıl partition yapıya geçirebilirim ?
    Bu hangi koşullarda karşımıza çıkar derseniz örneğin milyonlarca kayıt olan bir tabloyu arşivlemem gerekiyor, ve belirli tarihten öncesini silmek zorundayım. Bu oldukça sıkıntılı bir iştir.Tablo'da lock olma ihtimalinden, çok fazla io yapmaya kadar uzanan bir çile..
   Eğer benim partition bir tablom olsaydı silinmek istenen tarih aralığını arşivdeki tablo'ya switch edip bu dertden kurtulabilirdim.
   Kısa bir örnek ile neler yapabildiğimize bakalım. Öncelikle yeni bir tablo yaratıyorum.

Create Table members

( id int,

  name char(20),

  surname char(20),

  startDate smalldatetime

)

Boş olan tabloya biraz data girelim.

Declare @x int

Declare @id int

  set @x=0

  set @id=1

 while @x < 100

 begin

  insert into members values (@id,'ilker','usta','20110101')

  set @x=@x+1

  set @id=@id+1

 end

Bakalım ne kadar yerimiz oldu

sp_spaceused 'members'

Şimdi tablomuzun özelliğine bakalım

   Gördüğümüz gibi tablo PRIMARY yani default file gurup üzerinde yaratıldı. Şimdi gelelim asıl sorunumuza. Tablomuzda herhangi bir index yok. (olsada birşey değişmez) tabloya partition scheme üzerinde yeni bir index yaratırsak tablo primary den çıkıp partition yapıya geçmiş olur.

   Not : Kullanılan Partition scheme ve function daha önce yaratılmış type olarak smalldatetime dır. (startDate columundan partitions yapıyoruz.)

CREATE CLUSTERED INDEX [CIX] ON [dbo].[Members]

(

      [id] ASC

)WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)

ON [PS_MONTHLY_RANGE]([startDate])

GO

Şimdi tablomuzun özelliğine yeniden göz atalım

   Gördük ki tablomuz PS_MONTHLY_RANGE üzerine geçti. Artık tablomuza gelecek yeni kayıtlar startDate üzerinden aylık olarak faklı partitionlar da olucaktır......     Kolay gelsin :) 

Table partitioning

   Partitioning table yapısı benim kullanmasını en çok sevdiğim ve faydasına çok inandığım bir özelliktir. MS Sql server 2005 versiyonu ile gelen bu özellik pek çok yapıda kullanılabilir. 

   MS SQL Server Table Partitioning yapısı isminden de anlaşılacağı üzere tabloyu fiziksel olarak parçalamaktır. Buda hem io kullanımı hem disk kapasitesi ,tabloya gelen sorgu performansı bakımından çok önemli faydalar sağlamaktadır. Tablonun bu şekilde belirli tarih yada sınıf bakımından arşivlenmesi oldukça kolaylaştırır.

   Örneğin tablomuza tarihe göre bir sorgu geldiğini düşünelim ve tablomuzun yapısı tarihlere göre partition yapıda,  Sql server sorguyu uygun partitiona götürür ve sorgu bu fiziksel dosyada arar bulur. Buda sorgu performansını ciddi boyutlarda arttırmış olur.

   Şimdi gelin birlikte ufak bir uygulama yaratalım.

   Öncelikle partition function yaratalım ,ben ay ay partition kullanıcam ,bu yıl yıl yada belirlenen sınıflara göre de ayrılmış olabilir.

CREATE PARTITION FUNCTION [PF_MONTHLY_RANGE](smalldatetime) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00', N'2011-02-01T00:00:00', N'2011-03-01T00:00:00', N'2011-04-01T00:00:00', N'2011-05-01T00:00:00')

  Burda dikkat edilmesi gereken functionun kullanacağı column, Tarih columnu kullanacağımıza göre type smalldatetime verilmelidir. Şimdi sıra functionu kullanacak scheme yı yaratmakda. 

  Tabi bunun öncesinde her ay için yeni bir file gurup yaratıp bunlar için ayrı ayrı data file yaratmak gerekir , yada siz kendi yapınıza göre hepsini primary file gurupta yada herhangi bir file gurupta yaratabilirsiniz.

   Şimdi scheme yaratalım.

CREATE PARTITION SCHEME [PS_MONTHLY_RANGE] AS PARTITION [PF_MONTHLY_RANGE] TO([filegr], [filegr1], [filegr2], [filegr3], [filegr4], [filegr5])

   Dikkat ederseniz function da 2011 ocak dan başladık ve 5 partition numarası vardı. Scheme de ise 6 tane file gurup var, bu şu demek oluyor 2011 ocaktan küçük tarihler filegr dosyasına yazılır.

   Şimdi scheme üzerinde tablomuzu yaratalım.

CREATE TABLE CUS (id int, Tarih smalldatetime ) on PS_MONTHLY_RANGE(Tarih);

   Tablomuzu partition olarak yaratmış bulunuyoruz. Bu tabloya gelecek kayıtlar artık ay bazında farklı fiziksel dosyalara kayıt edilecekler.

   Hangi partition numarasında ne kadar kayıt olduğunu şu sorgudan görebilirsiniz.

select t.name,p.object_id,p.partition_id,p.index_id,p.partition_number,p.rows from sys.partitions p join sys.tables t

on p.object_id=t.object_id WHERE p.partition_number <> 1

   Table partitioning konusunda şimdilik anlatılacaklar bu kadar kolay gelsin :)