“Partitioned View” cara mempercepat akses data dalam Relational Data ware house
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0in;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}
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.
|--------- > Sales_Jan
|---------
> Sales_Feb
Table Sales_Data,dipecah |--------- > Sales_Mar
|---------
> Sales_Apr
|---------
> dst…
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.
Membuat 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:
- 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
Ada beberapa hal yang perlu
diperhatikan dalam menggunakan partitioned view ini yang berhubungan dengan
feature yang ada di SQL 2005. hal yang perlu diperhatikan itu adalah :
Aturan Table
·
Untuk table yang di select pada view yang dibuat hendaknya hanya di select
sekali saja untuk menjaga tidak ada data yang ditampilkan dua kali
·
setiap Fact table tidak boleh memiliki index yang nilainya computed dan Auto
increment.
·
Setiap table memiliki primary key yang sama.
·
setiap table hendaknya memiliki ANSI Padding yang sama.
Aturan Column
·
Semua Column dari setiap table harus termasuk dalam selection List di
partitioned view yang dibuat.
·
Setiap colum harus dituliskan sekali saja pada select statement untuk
setiap table
·
posisi column yang deselect harus sama urutannya pada setiap select statement
dari setiap fact table
·
setiap colum yang di select dari setiap table harus memiliki tipe data yang
sama.
Aturan Penentuan Column partisi
- 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.
Data modification
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.
Kesimpulan
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
Refferensi:
Berikut artikel
menarik tentang partitioned view termasuk perbandingan kecepatan aksesnya dan
link lain yang bermanfaat:
·
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp
·
http://www.sqlteam.com/item.asp?ItemID=684
·
http://www.sqlservercentral.com/columnists/njacobson/speedofdpv1.asp
·
http://www.sqlteam.com/item.asp?ItemID=751