SQL Geeks Indonesia

“Partitioned View” cara mempercepat akses data dalam Relational Data ware house

Apakah Partitioned View itu?

Jika kita bekerja dengan jumlah data besar apalagi jika dalam jumlah data mendekati tera byte,  maka kita akan menghadapi performance issue khususnya dalam mengakses data dalam jumlah row yang besar.

Ukuran table akan yang akan diakses akan sangat berpengaruh kepada waktu akses yang dibutuhkan, hal ini tidak dapat diatasi dengan hanya menentukan index column yang tepat pada suatu table, tentunya ukuran data akan sangat menentukan lama akses suatu table.

Sementara itu untuk keperluan data ware house availability dari data sebisa mungkin di keep selamanya didalam system seperti pada transaksi perbankan atau pun finance yang menuntut agar semua data dapat disimpan selama mungkin.

Untuk mengatasi masalah ini ada satu best practice yang bisa kita ikuti yaitu dengan memecah table menjadi partisi partisi data sehingga jumlah data akan selalu terjaga dan yang paling penting adalah waktu aksesnya tetap cepat.

Pemisahan data seperti ini tentunya tidak menjadi masalah ketika data yang diambil adalah data transaksi yang sifatnya mati dan tidak bergerak lagi. Untuk data yang sudah di close kita tinggal gunakan statement SELECT INTO atau INSERT INTO berdasarkan kelompok criteria yang diinginkan untuk contoh diatas berdasarkan bulan tertentu.

Proses pemecahan table diatas akan menjadi masalah jika data yang akan diakses merupakan data hidup dan kita perlu melakukan operasi CRUD (Create, Retreave, Update dan Delete) kedalamnya. Tentunya kita memerlukan SP atau TSQL yang smart untuk mengatasi operasi CRUD dengan memecah table tersebut.

Ternyata untuk memecahkan masalah ini tidak serumit yang kita bayangkan, SQL server 2000 menyediakan kemampuan untuk dapat membuat apa yang disebut sebagai Partitioned View.

Untuk membuat partitioned view memang menurut saya agak sedikit tricky, karena dengan cara membuat constrain di setiap fact table dan membuat view dari table – table tersebut maka kita sudah membuat partitioned view dari data yang kita inginkan. Hasilnya performance dari akses data tetap bisa dijaga.

Berikut langkah langkah untuk membuat partitioned view:

1. Buat partition table untuk fact table yang akan dibuat, contohnya sebagai berikut :

-- Creating the Tables

CREATE TABLE [dbo].[DATA_0501] (

  [CustomerId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [OrderId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [OrderDate] [datetime] NOT NULL 

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DATA_0502] (

  [CustomerId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [OrderId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [OrderDate] [datetime] NOT NULL 

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DATA_0503] (

  [CustomerId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [OrderId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

  [OrderDate] [datetime] NOT NULL 

) ON [PRIMARY]

GO

2.      Tentukan Constrain untuk setiap table sesuai dengan criteria data yang akan disimpan

-- Check Constraints

ALTER TABLE [dbo].[DATA_0501] WITH CHECK ADD CONSTRAINT CHKDATE_DATA_0501

  CHECK ([OrderDate] >= '1-Jan-2005' and [OrderDate] < '1-Feb-2005')

GO

ALTER TABLE [dbo].[DATA_0502] WITH CHECK ADD CONSTRAINT CHKDATE_DATA_0502

  CHECK ([OrderDate] >= '1-Feb-2005' and [OrderDate] < '1-Mar-2005')

GO

ALTER TABLE [dbo].[DATA_0503] WITH CHECK ADD CONSTRAINT CHKDATE_DATA_0503

  CHECK ([OrderDate] >= '1-Mar-2005' and [OrderDate] < '1-Apr-2005')

GO

Constrain CHECK akan mengaktifkan validasi data yang akan masuk kedalam table dimana kondisi data yang 

bisa dimasukkan (untuk Insert atau pun Update)  harus memenuhi syarat tertentu misalnya 

([OrderDate] >= '1-Jan-2005' and [OrderDate] < '1-Feb-2005'). 

Demikian juga untuk table yang lain akan divalidasi sesuai dengan constrain yang di tentukan pada expresi 

CHECK di setiap table.

3.      Kemudian buat Partition View untuk ketiga table tersebut dengan membuat view yang berisi union dari fact 

table yang sudah kita buat tadi

-- Create the PV

CREATE VIEW vw_DATA AS

  SELECT * FROM DATA_0501

  UNION ALL

  SELECT * FROM DATA_0502

  UNION ALL

  SELECT * FROM DATA_0503

GO

4.      Untuk mengakses Partitioned View ini dapat dilakukan dengan memanggil view tersebut seperti intruksi select 

pada table biasa

     -- Select Data

SELECT * FROM vw_DATA Where OrderDate = '12-Feb-2005'

-- Insert some data into base table

INSERT INTO DATA_0501 VALUES ('Customer1', 'Order1', '12-Jan-2005')

INSERT INTO DATA_0502 VALUES ('Customer2', 'Order2', '12-Feb-2005')

INSERT INTO DATA_0503 VALUES ('Customer3', 'Order3', '12-Mar-2005')

Demikian juga dengan intruksi update dengan delete, semua akan diatur oleh Constrain yang ada di table 

masing masing sehingga kita tidak perlu effort terlalu besar untuk mengatur mekanisme Insert, Update dan 

delete untuk setiap 

masing masing fact table.

Partitioned view akan mempercepat akses data karena setiap data yang diakses akan otomatis di tujukan pada fact 

table yang berhubungan dengan data yang dicari dan tidak melakukan select kepada semua fact table yang ada di 

definisi view.

Hal – Hal yang perlu diperhatikan

·         setiap colum yang di select dari setiap table harus memiliki tipe data yang sama.

• Column yang akan menjadi batasan partisi hendaknya memiliki constrain yang merupakan range data yang diharapkan dengan menggunakan operatorBETWEEN, AND, OR, <, <=, >, >=, =.
• Posisi Column yang menjadi partisi hendaknya ada pada posisi yang sama pada statement select union di View yang dibuat.
• Column yang akan dijadikan batas nilai partisi hendaknya merupakan primary key.

Untuk melakukan modifikasi hendaknya dilakukan dari partitioned view yang sudah dibuat, perubahan terhadap data yang langsung pada fact table akan berakibat data tidak akan tersimpan jika constrain data tidak terpenuhi pada fact table tersebut.

Jika kita menggunakan data yang sangat banyak mendekati terabyte sedangkan kita membutuhkan akses data yang cepat penggunakan partitioned view ini akan sangat membantu dalam meningkatkan performance dari akses data.

Masalah Data partition  ini pada SQL 2005 menjadi semakin mudah dan tidak tricky seperti di SQL 2000, karena pada SQL 2005 menyediakan fitur table partitioning yang mempercepat akses data dari setiap table yang kita kehendaki.  Untuk lebih jelas tentang table partitioning di SQL 2005 dapat dilihat di http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

Share this post: | | | |