چگونگی بخش بندی جداول در Oracle و مدیریت جداول بخش بندی شده
امروزه شرکت ها پایگاه داده هایی راه اندازی کرده و بکار می برند که حجم آن ها در برخی موارد به بیش از صدها گیگابایت هم می رسد. از این دست پایگاه های داده تحت عنوان VLDB (پایگاه داده های بسیار بزرگ و حجیم) نیز یاد می شود. از ویرایش 8.0 به بعد، Oracle قابلیت table partitioning (بخش بندی جداول) را ارائه داد که به وسیله ی آن می توان یک جدول را بنا به شرط یا معیارهای خاصی بخش بندی کرد. برای مثال، فرض بگیرید جدولی به نام SALES دارید که دارای ساختار زیر می باشد. حال در نظر بگیرید این جدول دربردارنده ی میلیون ها رکورد است و این رکورد ها همگی تنها متعلق به چهار سال 1991،1992 ، 1993 و 1994 می باشند. همچنین شما اغلب تنها با رکوردهای یک سال سروکار دارید و query هایی همانند مثال زیر صادر می کنید.
select sum(amt) from sales where year=1991;
select product,sum(amt) from sales where year=1992
Group by product;
هر زمان که query هایی مانند مثال بالا اجرا می کنید، Oracle شروع به جستجوی تمامی جدول می کند. چنانچه این جدول را بر اساس سال بخش بندی کنید، در این صورت Oracle بجای بررسی یا جستجوی کل جدول تنها یک بخش از آن را کاوش می کند و از این سرعت به طور چشم گیری افزایش می یابد.
نحوه ی بخش بندی جدول (ایجاد partition table)
برای بخش بندی جدول، دستور زیر را تایپ کنید.
create table sales (year number(4),
product varchar2(10),
amt number(10,2))
partition by range (year)
partition p1 values less than (1992) tablespace u1,
partition p2 values less than (1993) tablespace u2,
partition p3 values less than (1994) tablespace u3,
partition p4 values less than (1995) tablespace u4,
partition p5 values less than (MAXVALUE) tablespace u5;
مثال بالا جدول sales را به 5 بخش جز بندی کرده است. Partition p1 دربردارنده ی سطرهای سال 1991 بوده و در tablespace u1 ذخیره می گردد. Partition p2 سطرهای متعلق به سال 1992 را دربر می گیرد و در tablespace u2 ذخیره می گردد. p3 و p4 نیز همین رویه را دنبال می کنند.
در مثال بالا، اگر partition p4 را با مقادیری کوچکتر از MAXVALUE تعریف نکنید، دیگر قادر نخواهید بود هیچ سطری که متعلق به سالی بالاتر از 1994 می باشد، درج کنید.
در صورت تمایل می توان partition ها را در tablespace های مختلف قرار داد. اگر partition ها را در tablespace (مکانی است که در آن، اطلاعاتی که پایگاه دادهها را شکل میدهند نگهداری و ذخیره می شوند) های مختلف قرار دهید، در آن صورت می توانید مشکلات (نشات گرفته از خرابی) را از هم جدا کنید، بدین معنا که خرابی یک tablespace باعث نمی شود که partitionهایی که در tablespace های دیگر قرار دارند از کار بیافتند.
در مثال بالا، جدول بر اساس range بخش بندی (partition) می شود.
در oracle می توان یک جدول را به چند روش بخش بندی که
- Range Partitioning
- Hash Partitioning
- List Partitioning
- Composite Partitioning
Range Partitioning
این نوع بخش بندی به خصوص در مواقعی بسیار کارامد تلقی می گردد که با داده هایی سروکار داریم که دارای دامنه های منطقی (logical range) هستند و می توانند در آن دامنه توزیع شوند؛ برای مثال مقدار یک سال. Range partitioning یک تکنیک بخش بندی است که دامنه ای از داده ها به صورت جداگانه در پارتیشنهای مختلفی ذخیره می شوند. کارایی زمانی بهینه می شود که داده به طور مساوی در سرتاسر دامنه (range) توزیع شوند.
Hash partitioning
از این نوع بخش بندی زمانی بهره می گیریم که امکان اجرای range partitioning وجود نداشته باشد، اما شما می خواهید برای قابلیت مدیریت بهتر و افزایش کارایی، partition بندی انجام دهید. Hash partitioning یک روش برای توزیع داده به طور مساوی در تعداد مشخصی partition را فراهم می آورد. به عبارت دیگر Hash partitioning یک تکنیک بخش بندی است که در آن با استفاده از یک hash key (کلید درهم سازی هش) می توان سطرها را به طور مساوی بین partition های (پارتیشن) مختلف توزیع کرد.
مثال زیر نحوه ی ایجاد hash partition table را برای شما نمایش می دهد.
این مثال یک جدول hash-partitioned ایجاد می کند. Partno، Partitioning column است. چهار پارتیشن (partition) ایجاد شده و اسامی تولید شده توسط سیستم به آن ها تخصیص داده می شود، سپس در چهار tablespace نام گذاری شده (tab1,tab2, ...) جای گذاری می شوند.
CREATE TABLE products
(partno NUMBER,
description VARCHAR2 (60))
PARTITION BY HASH (partno)
PARTITIONS 4
STORE IN (tab1, tab2, tab3, tab4);
List Partitioning
از این روش بخش بندی زمانی استفاده کنید که می خواهید کنترل صریح بر روی نحوه ی نگاشت (map) سطرها به پارتیشن ها (partition) داشته باشید. می توان لیستی از مقادیر جداگانه را برای partitioning key در تعریف هر پارتیشن مشخص کرد. این روش با range partitioning متفاوت است که در آن دامنه یا طیفی از مقادیر به یک پارتیشن (partition) مربوط و متصل هستند و همچنین hash partitioning که در آن کاربر هیچ کنترلی بر نگاشت سطر به پارتیشن ها ندارد.
روش پارتیشن بندی مزبور این امکان را برای مجموعه داده های نامرتب و نامربوط فراهم می کند که به صورت کاملا طبیعی با هم مرتب، گروه بندی و سازمان دهی شوند.
مثال:
Create table customers (custcode number(5),
Name varchar2(20),
Addr varchar2(10,2),
City varchar2(20),
Bal number(10,2))
Partition by list (city),
Partition north_India values (‘DELHI’,’CHANDIGARH’),
Partition east_India values (‘KOLKOTA’,’PATNA’),
Partition south_India values (‘HYDERABAD’,’BANGALORE’,
’CHENNAI’),
Partition west India values (‘BOMBAY’,’GOA’);
COMPOSITE PARTITONING
یک روش پارتیشن بندی است که روش های دیگر را با هم تلفیق می کند. جدول مورد نظر ابتدا توسط روش اول توزیع داده (مثلا روش range) بخش بندی شده، سپس هر یک از پارتیشن های (partition) حاصل خود توسط روش دوم توزیع داده (مثلا hash) sub-partition می شود.
به هنگام ایجاد composite partitions، باید اقلام زیر را تعریف کنید.
- روش پارتیشن بندی: range
- Partitioning column(s)
- Partition descriptions که کران یا محدوده ی (bound) پارتیشن را تعیین می کند
- روش sub-partition: hash
- تعداد زیر بخش (sub partition) به ازای هر partition (subpartition description)
مثال زیر یک جدول composite-partitioned را ایجاد می کند.
در این مثال، سه range partition ایجاد می شود که هر یک خود دربردانده ی هشت زیر بخش (sub partition) می باشد. به این خاطر که زیر بخش ها نام گذاری نشده اند، خود سیستم به آن ها اسامی را تخصیص می دهد، اما عبارت STORE IN آن ها را بین چهار tablespace (tab1, ...,tab4) پخش می کند.
CREATE TABLE PRODUCTS (partno NUMBER,
description VARCHAR(32),
costprice NUMBER)
PARTITION BY RANGE (partno)
SUBPARTITION BY HASH(description)
SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4)
(PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
اصلاح partition table ها
افزودن یک پارتیشن
می توان یک partition جدید به انتهای جدول (نقطه ی بعد از آخرین پارتیشن موجود) افزود. به منظور اضافه کردن یک پارتیشن به ابتدا یا انتهای جدول موجود، می توان از عبارت SPLIT PARTITION بهره گرفت.
به عنوان مثال برای افزودن یک پارتیشن جدید به جدول sales، دستور زیر را اجرا می کنیم:
alter table sales add partition p6 values less than (1996);
به منظور افزودن یک جدول hash partition نیز دستور زیر را تایپ می کنیم.
Alter table products add partition;
Oracle یک partition جدید که اسم آن توسط سیستم تولید و تخصیص داده می شود، اضافه می کند. این partition در tablespace پیش فرض ایجاد می شود. حال جهت افزودن یک partition جدید که اسم آن توسط کاربر تعریف شده باشد و همچنین در tablespace دلخواه ایجاد شود، کافی است دستور زیر اجرا کنید.
Alter table products add partition p5 tablespace u5;
برای اضافه کرن یک پارتیشن به List partition table (جدولی که به روش list بخش بندی شده باشد)، دستور زیر را بکار می بریم.
alter table customers add partition central_India
values (‘BHOPAL’,’NAGPUR’);
coalescing partitions
روشی است برای کاهش تعداد پارتیشن های موجود در یک جدول hash-partitioned و یا تعداد زیر بخش های (sub partition) درون یک جدول composite-partitioned. هنگامی که یک hash partition، coalesce می شود، تمامی محتویات آن مجددا در یک یا چند پارتیشن باقی مانده که توسط hash function (تابع درهم سازی) تعیین می شود، پخش می گردد. پارتیشنی که قرار است coalesce شود، توسط oracle انتخاب می گردد و پس از اینکه محتویات آن مجددا پخش شدند، پارتیشن نام برده حذف می گردد.
برای coalesce کردن یک hash partition، دستور زیر را مورد استفاده قرار می دهیم.
Alter table products coalesce partition;
این دستور یکی از تعداد پارتیشن های موجود را می کاهد.
حذف پارتیشن ها
به منظور حذف یک پارتیشن از Range Partition ، Composite Partition یا List Partition کافی است دستور زیر را بکار ببرید.
Alter table sales drop partition p5;
اگر یک اندیس سراسری بر روی جدول ایجاد کرده بودید، پس از حذف زیرجدول دوباره مجبور می شوید اندیس سراسری را ایجاد کنید. برای این منظور دستور زیر را بکار می بریم:
Alter index sales_ind rebuild;
برای اجتناب از ایجاد مجدد اندیس ها (پس از حذف پارتیشن)، می توان ابتدا تمامی رکوردها را حذف کرد و بعد پارتیشن (partition) را حذف کرد، بدین ترتیب
Delete from sales where year=1994;
Alter table sales drop partition p4;
این روش برای جداول کوچک و همچنین جداول بزرگ هنگامی که پارتیشنی که باید حذف شود دربردارنده ی تنها جزئی از کل داده های موجود در جدول می باشد، مناسب است.
روش دیگری برای حذف پارتیشن ها وجود دارد و آن استفاده از دستور زیر می باشد.
ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;
این دستور باعث می شود، اندیس سراسری به هنگام حذف partition، بروز رسانی شود.
معاوضه ی partition های یک جدول بخش بندی شده با یک جدول بخش بندی نشده
به منظور تعویض partition های یک جدول بخش بندی شده با یک جدول بخش بندی نشده، یا بالعکس، دستور ALTER TABLE ... EXCHANGE PARTITION را بکار می بریم. مثالی از تبدیل یک partition به یک جدول بخش بندی نشده را در زیر مشاهده می کنید. در این مثال جدول table stocks می تواند بر اساس range، hash، list بخش بندی شود.
ALTER TABLE stocks
EXCHANGE PARTITION p3 WITH stock_table_3;
ادغام پارتیشن ها (merging partition)
با استفاده از دستور ALTER TABLE ... MERGE PARTITIONS می توان محتویات دو partition را با هم ترکیب کرده و در یک partition جدید گنجاند. پیرو اجرای این عملیات، دو partition حذف شده و به همراه آن تمامی اندیس های مربوطه نیز حذف می گردند.
نمی توان این دستور را برای جداول hash-partitioned یا subpartition های یک جدول composite-partitioned بکار برد.
تنها امکان ادغام دو partition مجاور با هم وجود دارد، بدین معنا که نمی توان دو partition غیر مجاور را باهم ترکیب کرد.
برای مثال، به منظور ترکیب کردن دو پارتیشن p2 و p3و گنجاندن آن ها در پارتیشن p23، دستور زیر را صادر می کنیم.
Alter table sales merge partition p2 and p3 into
partition p23;
اصلاح partition ها: افزودن مقادیر جدید
با استفاده از عبارت MODIFY PARTITION ... ADD VALUES (زیر مجموعه ی دستور ALTER TABLE) می توان لیست مقادیر partition موجود را بسط داد. مقادیر literal ای (به نشانههایی در source code برنامه گفته میشود که در زمان کامپایل به مقادیر مشخصی تفسیر میشوند. به عبارت دیگر literal ها مقادیر ثابتی هستند که به صورت نشانههایی در کد برنامه نوشته میشوند) که اضافه می شوند نباید به هیچ وجه در لیست مقادیر partition دیگری لحاظ (include) شده باشند. دستور زیر تعدادی شهر جدید ('KOCHI', 'MANGALORE') را به لیست پارتیشن موجود اضافه می کند.
ALTER TABLE customers
MODIFY PARTITION south_india
ADD VALUES ('KOCHI', 'MANGALORE');
اصلاح partition ها: حذف مقادیر
به وسیله ی عبارت MODIFY PARTITION ... DROP VALUES (زیر مجموعه ی دستور ALTER (TABLE می توان مقادیر نوشتاری (literal values) مورد نظر را از لیست مقادیر پارتیشن موجود حذف کرد. با اجرای دستور، اعتبار سنجی صورت داده می شود، بدین معنا که oracle بررسی می کند آیا سطرهایی در پارتیشن وجود دارد که با مجموعه مقادیر مورد حذف (مقادیری که به وسیله ی دستور مذکور از لیست مقادیر پارتیشن پاک می شوند) مرتبط باشد یا خیر. در صورتی که چنین سطری یافت شود، Oracle یک پیغام خطا برمی گرداند و به دنبال آن عملیات با شکست مواجه می شود.
در صورت لزوم، سعی کنید ابتدا به کمک دستور DELETE سطرهای متصل با این مقادیر را از جدول حذف کرده، سپس اقدام به حذف مقادیر مورد نظر کنید.
شما نمی توانید تمامی مقادیر نوشتاری (literal values) را از لیست مقادیر (تعریف کننده ی) پارتیشن حذف کنید. برای این منظور توصیه می شود از دستور ALTER TABLE ... DROP PARTITION بهره بگیرید.
دستور زیر تعدادی از شهرها (‘KOCHI' , 'MANGALORE') را از لیست مقدار پارتیشن موجود حذف می کند.
ALTER TABLE customers
MODIFY PARTITION south_india
DROP VALUES (‘KOCHI’,’MANGALORE’);
دو نیم کردن partition
می توان یک پارتیشن واحد را به دو پارتیشن تقسیم کرد. به عنوان مثال، برای تقسیم partition p5 متعلق به جدول sales به دو بخش، دستور زیر را اعمال می کنیم.
Alter table sales split partition p5 into
(Partition p6 values less than (1996),
Partition p7 values less then (MAXVALUE));
حذف سطرهای یک پارتیشن (truncating partition)
Truncate کردن پارتیشن باعث می شود تمامی سطرهای آن پارتیشن حذف شوند.
به منظور حذف سطرهای یک پارتیشن، کافی است دستور زیر را اجرا کنید.
Alter table sales truncate partition p5;
بازیابی اطلاعات درباره ی پارتیشن ها
به منظور بازیابی و مشاهده ی اطلاعات درباره ی تعداد پارتیشن های موجود در schema خود، کافی است دستور زیر را بکار ببرید.
Select * from user_part_tables;
برای مشاهده ی اطلاعات سطح پارتیشن از دستور زیر استفاده می کنیم.
Select * from user_tab_partitions;