Partition نمودن جدول موجود بصورت آنلاین


تاریخ انتشار: 1398/فروردین/29
نویسنده:مهندس امیر رستنده
گروه دانش : Oracle Datebase
تعداد بازدید: 10 مرتبه
اشتراک گذاری:
<h1><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 12pt;">"Partitioning an Existing Table using DBMS_REDEFINITION"</span>
</h1>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;">در این مقاله نحوه پارتیشن نمودن یک جدول موجود را با استفاده از پکیج DBMS_REDEFINITION &nbsp;توضیح خواهم داد.</span>
</p>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;">لازم بذکر است که این مطلب تنها در مورد چگونگی انجام اینکار بوده و در خصوص اهداف و مزایای استفاده از Partitioning در مقاله دیگری صحبت خواهم کرد. تنها به یاد داشته باشید که در بسیاری از موارد پارتیشن نمودن اشتباه یک جدول به مراتب بدتر از جدول بدون پارتیشن خواهد بود.</span>
</p>
<hr id="system-readmore" />
<h2><strong><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 12pt;" lang="FA" dir="RTL">آماده سازی محیط تست</span></strong>
</h2>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;" lang="AR-SA" dir="RTL">ابتدا جهت آماده سازی محیط تست نیاز به ایجاد تعدادی جدول با مشخصات زیر خواهیم داشت:</span>
</p><pre>-- Create and populate a small lookup table.
CREATE TABLE lookup (
id NUMBER(10),
description  VARCHAR2(50)
);
ALTER TABLE lookup ADD (
 CONSTRAINT lookup_pk PRIMARY KEY (id)
);<br />
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;<br />
-- Create and populate a larger table that we will later partition.
CREATE TABLE <strong>big_table</strong> (
id           NUMBER(10),
created_date DATE,
lookup_id    NUMBER(10),
data         VARCHAR2(50)
);<br />
DECLARE
 l_lookup_id    lookup.id%TYPE;
 l_create_date  DATE;
BEGIN
 FOR i IN 1 .. 1000000 LOOP 
   IF MOD(i, 3) = 0 THEN
     l_create_date := ADD_MONTHS(SYSDATE, -24);
     l_lookup_id   := 2;
   ELSIF MOD(i, 2) = 0 THEN
     l_create_date := ADD_MONTHS(SYSDATE, -12);
     l_lookup_id   := 1;
   ELSE
     l_create_date := SYSDATE;
     l_lookup_id   := 3;
   END IF;
 <br /> INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
 END LOOP;<br /> COMMIT;<br />END;<br />
-- Apply some constraints to the table.
ALTER TABLE big_table ADD (
 CONSTRAINT big_table_pk PRIMARY KEY (id)
);<br />
CREATE INDEX bita_created_date_i ON big_table(created_date);
CREATE INDEX bita_look_fk_i ON big_table(lookup_id);<br />
ALTER TABLE big_table ADD (
CONSTRAINT bita_look_fk FOREIGN KEY (lookup_id) REFERENCES lookup(id)
);<br />
-- Gather statistics on the schema objects
EXEC DBMS_STATS.gather_table_stats(USER, 'LOOKUP', cascade =&gt; TRUE);
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE', cascade =&gt; TRUE);
</pre>
<h2><strong><span style="font-size: 12pt; font-family: tahoma, arial, helvetica, sans-serif;">ایجاد جدول موقت - Interim Table</span></strong>
</h2>
<p><span style="font-size: 10pt; font-family: tahoma, arial, helvetica, sans-serif;">سپس یک جدول با پارتیشن های مورد نظر ایجاد می کنیم. اصطلاحا به آن Interim Table نیز می گویند .<br />این جدول در فرآیند انتقال داده ها به پارتیشن ها مورد استفاده قرار می گیرد.</span>
</p><pre>-- Create partitioned table. 
CREATE TABLE <strong>big_table2</strong> ( 
id NUMBER(10), 
created_date DATE, 
lookup_id NUMBER(10), 
data VARCHAR2(50) 

PARTITION BY RANGE (created_date) 
(PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')), 
PARTITION big_table_2014 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')), 
PARTITION big_table_2015 VALUES LESS THAN (MAXVALUE) 
);
</pre>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;"><span lang="AR-SA" dir="RTL">با استفاده از این جدول موقتی می توانیم عمل </span>&nbsp;partitioning <span lang="FA" dir="RTL">را بصورت آنلاین روی جدول مورد نظر اعمال نماییم.</span></span>
</p>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif;"><span style="font-size: 16px;"><strong>آغاز فرآیند Redefinition</strong></span></span>
</p>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;">ابتدا با دستور زیر بررسی می کنیم که ایا جدول مورد نظر قابلیتRedefinition را دارد یا خیر؟</span>
</p><pre>EXEC Dbms_Redefinition.Can_Redef_Table(USER, 'BIG_TABLE');</pre>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif;"><span style="font-size: 13.3333330154419px;">اگر پس از اجرای دستور فوق خطایی مشاهده نکردید می توانید فرآیند Redefinition را آغاز نمایید:</span></span>
</p><pre>BEGIN
 DBMS_REDEFINITION.start_redef_table(
   uname =&gt; USER,
   orig_table =&gt; 'BIG_TABLE',
   int_table =&gt; 'BIG_TABLE2');
END;
/
</pre>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;" lang="AR-SA" dir="RTL">بسته به اینکه اندازه جدول شما چقدر است مدت زمان اجرای این دستور می تواند متفاوت باشد.</span>
</p>
<h2><strong><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 12pt;">ایجاد Index ها و Constraint ها</span></strong>
</h2>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;">قبل از ساخت ایندکس ها توصیه می شود که جدول موقتی را resynchronize نمایید.</span>
</p><pre>-- Optionally synchronize new table with interim data before index creation
BEGIN
 dbms_redefinition.sync_interim_table(
   uname =&gt; USER,
   orig_table =&gt; 'BIG_TABLE',
   int_table&nbsp; =&gt; 'BIG_TABLE2');
END;
/
</pre>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif;"><span style="font-size: 13.3333330154419px;">و در نهایت می توانید با استفاده از کد زیر کلیه object هایی را که در جدول وجود داشته به جدول جدید منتقل نمایید.</span></span>
</p>
<p>&nbsp;</p><pre>SET SERVEROUTPUT ON
DECLARE
 l_errors NUMBER;<br />
BEGIN
 DBMS_REDEFINITION.copy_table_dependents(
   uname =&gt; USER,
   orig_table =&gt; 'BIG_TABLE', 
   int_table =&gt; 'BIG_TABLE2',
   copy_indexes =&gt; DBMS_REDEFINITION.cons_orig_params ,
   copy_triggers =&gt; TRUE,
   copy_constraints =&gt; TRUE,
   copy_privileges&nbsp; =&gt; TRUE,
   ignore_errors =&gt; FALSE,
   num_errors =&gt; l_errors,
   copy_statistics =&gt; FALSE,
   copy_mvlog =&gt; FALSE
   );
 DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/
</pre>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif;"><span style="font-size: 13.3333330154419px;">روش دیگر اینست که خود شما بصورت دستی اشیاء وابسته و مرتبط به جدول را مجددا ایجاد نمایید:</span></span>
</p><pre>-- Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
 CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);<br />
CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;
CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;<br />
ALTER TABLE big_table2 ADD (
 CONSTRAINT bita_look_fk2 FOREIGN KEY (lookup_id)  REFERENCES lookup(id)
);<br />
-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats(USER, 'BIG_TABLE2', cascade =&gt; TRUE);
</pre>
<h2>پایان رساندن فرآیند Redefinition</h2>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;">پس از ساخت ایندکس ها و Constraint ها برروی جدول جدید باید قرآیند Redefinition را با دستور زیر کامل نمایید.</span>
</p><pre>BEGIN
 dbms_redefinition.finish_redef_table(
 uname =&gt; USER,
 orig_table =&gt; 'BIG_TABLE',
 int_table&nbsp; =&gt; 'BIG_TABLE2');
END;
/</pre>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;" lang="FA" dir="RTL">بعد از پایان موفقیت آمیز مرحله بالا می توانید جدول موقتی را حذف نموده و یکسری عملیات پاکسازی نظیر تغییر نامها را برروی جدول خود انجام دهید.</span>
</p><pre>-- Remove original table which now has the name of the interim table.
DROP TABLE big_table2;<br />
-- Rename all the constraints and indexes to match the original names.
ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;
</pre>
<p><strong><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 12pt;" lang="AR-SA" dir="RTL">بررسی صحت عملیات</span></strong>
</p>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;" lang="AR-SA" dir="RTL">با استفاده از کوئری های زیر می توانید صحت انجام عملیات فوق را بررسی نمایید</span>
</p><pre>SELECT partitioned
  FROM user_tables
  WHERE&nbsp; table_name = 'BIG_TABLE';<br />
PAR
---
YES<br />
1 row selected.<br /><br />
SELECT partition_name
 FROM&nbsp;&nbsp; user_tab_partitions
 WHERE&nbsp; table_name = 'BIG_TABLE';
 
PARTITION_NAME
------------------------------
BIG_TABLE_2013
BIG_TABLE_2014
BIG_TABLE_2015<br />
3 rows selected.<br /><br /><br /></pre>
<p><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;">امیدوارم مفید باشه</span><br /><span style="font-family: tahoma, arial, helvetica, sans-serif; font-size: 10pt;">حمید قاسمی</span>
</p>
<p>&nbsp;</p>