جداول پارتیشن شده هیبریدی در پایگاه داده اوراکل ۱۹C

این مقاله نحوه امکان ترکیب پارتیشن های داخلی و خارجی را در یک جدول پاتیشن شده هیبریدی در پایگاه داده اوراکل ۱۹C نشان می دهد.

جداول پارتیشن شده هیبریدی از انواع جداول External ذیل جهت پارتیشنینگ خارجی (External) پشتیبانی می کنند.

• ORACLE_DATAPUMP

• ORACLE_LOADER

• ORACLE_HDFS

• ORACLE_HIVE

در این مقاله بر روی مثالهایی از ORACLE_DATAPUMP و ORACLE_LOADER متمرکز میشویم.

• Setup
• ایجاد جدول پارتیشن شده ترکیبی (ORACLE_LOADER)
• ایجاد جدول پارتیشن شده ترکیبی (ORACLE_DATAPUMP)
• اضافه و حذف پارتیشن ها
• محدودیتها

Setup

با نام کاربری با دسترسی DBA، یک نام کاربری تستی با نام TESTUSER1 ایجاد می کنیم.

CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA

–DROP USER testuser1 CASCADE;

CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users;

GRANT CREATE SESSION, CREATE TABLE TO testuser1;

دو دایرکتوری (Directory Object) ایجاد و به یوزر TESTUSER1 بر روی هر دو دایرکتوری دسترسی Read، Write و Execute می دهیم.

CREATE OR REPLACE DIRECTORY TMP_DIR1 AS ‘/tmp/’;

GRANT READ, WRITE, EXECUTE ON DIRECTORY TMP_DIR1 TO testuser1;

CREATE OR REPLACE DIRECTORY TMP_DIR2 AS ‘/tmp/’;

GRANT READ, WRITE, EXECUTE ON DIRECTORY TMP_DIR2 TO testuser1;

مابقی مراحل با نام کاربری TESTUSER1 انجام می شود.

CONN testuser1/testuser1@//localhost:1521/pdb1

ایجاد جدول پارتیشن شده ترکیبی (ORACLE_LOADER)

یک سری Flat File (بطور مثال فایل CSV) با داده های ساختگی برای استفاده پارتیشن جداول خارجی (External) ایجاد می نماییم.

SET MARKUP CSV ON QUOTE ON

SET TRIMSPOOL ON LINESIZE 1000 FEEDBACK OFF PAGESIZE 0

SPOOL /tmp/gbr1.txt

SELECT ‘GBR’,

object_id,

owner,

object_name

FROM all_objects

WHERE object_id <= 3999

AND rownum <= 1000;

SPOOL OFF

SPOOL /tmp/gbr2.txt

SELECT ‘GBR’,

object_id,

owner,

object_name

FROM all_objects

WHERE object_id BETWEEN 4000 AND 5999

AND rownum <= 1000;

SPOOL OFF

SPOOL /tmp/ire1.txt

SELECT ‘IRE’,

object_id,

owner,

object_name

FROM all_objects

WHERE object_id <= 3999

AND rownum <= 1000;

SPOOL OFF

SPOOL /tmp/ire2.txt

SELECT ‘IRE’,

object_id,

owner,

object_name

FROM all_objects

WHERE object_id BETWEEN 4000 AND 5999

AND rownum <= 1000; SPOOL OFF SET MARKUP CSV OFF SET TRIMSPOOL ON LINESIZE 1000 FEEDBACK ON PAGESIZE 14

در مثال ذیل یک جدول پارتیشن شده هیبریدی یا ترکیبی ایجاد می کنیم، که در آن از پارتیشنینگ نوع list به همراه یک پارتیشن داخلی یا internal برای دیتای USA و دو پارتیشن خارجی با External برای دیتای GBR و IRE استفاده میگردد.

–DROP TABLE test_ol_hybrid_part_tab PURGE;

CREATE TABLE test_ol_hybrid_part_tab ( country_code VARCHAR2(3) NOT NULL, object_id NUMBER NOT NULL, owner VARCHAR2(128) NOT NULL, object_name VARCHAR2(128) NOT NULL ) EXTERNAL PARTITION ATTRIBUTES ( TYPE ORACLE_LOADER DEFAULT DIRECTORY tmp_dir1 ACCESS PARAMETERS ( FIELDS CSV WITH EMBEDDED TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ MISSING FIELD VALUES ARE NULL (country_code, object_id, owner, object_name) ) REJECT LIMIT UNLIMITED ) PARTITION BY LIST (country_code) ( PARTITION usa VALUES (‘USA’), PARTITION gbr VALUES (‘GBR’) EXTERNAL LOCATION (‘gbr1.txt’, ‘gbr2.txt’), PARTITION ire VALUES (‘IRE’) EXTERNAL DEFAULT DIRECTORY tmp_dir2 LOCATION (‘ire1.txt’, ‘ire2.txt’) );

در بند EXTERNAL PARTITION ATTRIBUTES پارامترهای در سطح جدول (Table-level) مربوط به جدول خارجی را تعریف می نماییم و برای پارتیشنهای خارجی، از بند External یا خارجی برای تعریف ویژگیهای در سطح پارتیشن مانند مکان فایلها یا Directory object های غیر پیش فرض استفاده می شود. ستون HYBRID در view های CDB|DBA|ALL|USER}_TABLES نشان می دهد که این جدول از نوع جدول هیبرید می باشد.

COLUMN table_name FORMAT A30 COLUMN hybrid FORMAT A6

SELECT table_name, hybrid FROM user_tables;

TABLE_NAME HYBRID TEST_OL_HYBRID_PART_TAB YES

SQL>

در حال حاضر فقط در پارتیشن های خارجی (external partitions) دیتا داریم.

COLUMN country_code FORMAT A12

SELECT country_code, COUNT(*) as amount

FROM test_ol_hybrid_part_tab

GROUP BY country_code

ORDER BY country_code;

COUNTRY_CODE AMOUNT

GBR 2000

IRE 2000

SQL>

می توانیم دستورات DML را روی پارتیشنهای داخلی به صورت نرمال اجرا کنیم.

INSERT INTO test_ol_hybrid_part_tab

SELECT ‘USA’,

object_id,

owner,

object_name

FROM all_objects

WHERE rownum <= 2000;

COMMIT;

COLUMN country_code FORMAT A12

SELECT country_code, COUNT(*) as amount FROM test_ol_hybrid_part_tab GROUP BY country_code ORDER BY country_code; COUNTRY_CODE AMOUNT GBR 2000 IRE 2000 USA 2000

SQL>

اما همانطور که انتظار می رود، عملیات insert بر روی پارتیشنهای خارجی با Error همراه است.

INSERT INTO test_ol_hybrid_part_tab VALUES (‘GBR’, 9999, ‘X’, ‘X’);

*

ERROR at line 1:

ORA-14466: Data in a read-only partition or subpartition cannot be modified.

SQL>

ایجاد جدول پارتیشن شده ترکیبی (ORACLE_DATAPUMP)

در ابتدا یک dump file به نام bgr_xt.dmp با محتوای دیتای BGR ایجاد می کنیم، با ایجاد جدول خارجی فایل dump تولید می شود و زمانیکه جدول را drop کنیم فایل dump در در file system دیتابیس باقی می ماند. تا بتوانیم برای تستمان از آن استقاده کنیم.

CREATE TABLE bgr_xt

ORGANIZATION EXTERNAL

(

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY tmp_dir1

LOCATION (‘bgr_xt.dmp’)

) AS

SELECT ‘BGR’ AS country_code,

object_id,

owner,

object_name

FROM all_objects

WHERE rownum <= 2000; DROP TABLE bgr_xt;

در مثال ذیل یک جدول پارتیشن شده هیبریدی یا ترکیبی ایجاد می کنیم، که در آن از پارتیشنینگ نوع list به همراه یک پارتیشن داخلی یا internal برای دیتای USA و پارتیشن های Data pump خارجی برای دیتای BGR استفاده می شود.

–DROP TABLE test_dp_hybrid_part_tab PURGE;

CREATE TABLE test_dp_hybrid_part_tab ( country_code VARCHAR2(3) NOT NULL, object_id NUMBER NOT NULL, owner VARCHAR2(128) NOT NULL, object_name VARCHAR2(128) NOT NULL ) EXTERNAL PARTITION ATTRIBUTES ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY tmp_dir1 ) PARTITION BY LIST (country_code) ( PARTITION usa VALUES (‘USA’), PARTITION bgr VALUES (‘BGR’) EXTERNAL LOCATION (‘bgr_xt.dmp’) );

در بند EXTERNAL PARTITION ATTRIBUTES پارامترهای در سطح جدول (Table-level) مربوط به Data pump جدول خارجی را تعریف می نماییم و برای پارتیشنهای خارجی، از بند External یا خارجی برای تعریف ویژگیهای در سطح پارتیشن مانند مکان فایلها یا Directory object های غیر پیش فرض استفاده می شود. ستون HYBRID در view های CDB|DBA|ALL|USER}_TABLES نشان می دهد که این جدول از نوع جدول هیبرید می باشد.

COLUMN table_name FORMAT A30

COLUMN hybrid FORMAT A6

SELECT table_name, hybrid FROM user_tables;

TEST_OL_HYBRID_PART_TAB YES TEST_DP_HYBRID_PART_TAB YES

SQL>

We only have data in the external partitions at the moment.

COLUMN country_code FORMAT A12

SELECT country_code, COUNT(*) as amount

FROM test_dp_hybrid_part_tab

GROUP BY country_code

ORDER BY country_code;

COUNTRY_CODE AMOUNT

BGR 2000

SQL>

می توانیم دستورات DML را روی پارتیشنهای داخلی به صورت نرمال اجرا کنیم.

INSERT INTO test_dp_hybrid_part_tab

SELECT ‘USA’,

object_id,

owner,

object_name

FROM all_objects

WHERE rownum <= 2000; COMMIT;

COLUMN country_code FORMAT A12

SELECT country_code, COUNT(*) as amount FROM test_dp_hybrid_part_tab GROUP BY country_code ORDER BY country_code;

COUNTRY_CODE AMOUNT BGR 2000 USA 2000

SQL>

اما همانطور که انتظار می رود، عملیات insert بر روی پارتیشنهای خارجی با Error همراه است.

INSERT INTO test_dp_hybrid_part_tab VALUES (‘GBR’, 9999, ‘X’, ‘X’);

*

ERROR at line 1:

ORA-14466: Data in a read-only partition or subpartition cannot be modified.

SQL>

اضافه و حذف پارتیشن ها

می توانیم حذف و اضافه نمودن پارتیشن ها را بصورت معمول انجام دهیم.
در این مثال پارتیشن مربوط به دیتای IRE را از جدول TEST_OL_HYBRID_PART_TAB حذف میکنیم.

ALTER TABLE test_ol_hybrid_part_tab DROP PARTITION ire;

COLUMN country_code FORMAT A12

SELECT country_code, COUNT(*) as amount

FROM test_ol_hybrid_part_tab

GROUP BY country_code

ORDER BY country_code;

COUNTRY_CODE AMOUNT

GBR 2000

USA 2000

SQL>

حالا پارتیشن مربوط به دیتای IRE را مجددا اضافه می نماییم.

ALTER TABLE test_ol_hybrid_part_tab ADD PARTITION ire VALUES (‘IRE’) EXTERNAL

DEFAULT DIRECTORY tmp_dir2 LOCATION (‘ire1.txt’, ‘ire2.txt’);

COLUMN country_code FORMAT A12

SELECT country_code, COUNT(*) as amount

FROM test_ol_hybrid_part_tab

GROUP BY country_code

ORDER BY country_code;

COUNTRY_CODE AMOUNT

GBR 2000

IRE 2000

USA 2000

SQL>

در این مثال پارتیشن مربوط به دیتای BGR را از جدول TEST_OL_HYBRID_PART_TAB حذف میکنیم.

ALTER TABLE test_dp_hybrid_part_tab DROP PARTITION bgr;

COLUMN country_code FORMAT A12

SELECT country_code, COUNT(*) as amount

FROM test_dp_hybrid_part_tab

GROUP BY country_code

ORDER BY country_code;

COUNTRY_CODE AMOUNT

USA 2000

SQL>

مجددا پارتیشن مربوط به دیتای BGR را اضافه می نماییم.

ALTER TABLE test_dp_hybrid_part_tab ADD PARTITION bgr VALUES (‘BGR’) EXTERNAL

DEFAULT DIRECTORY tmp_dir2 LOCATION (‘bgr_xt.dmp’);

COLUMN country_code FORMAT A12

SELECT country_code, COUNT(*) as amount

FROM test_dp_hybrid_part_tab

GROUP BY country_code

ORDER BY country_code;

COUNTRY_CODE AMOUNT

BGR 2000

USA 2000

SQL>

همانطور که انتظار می رود عملیات حذف و اضافه نمودن پارتیشن ها بر روی پارتیشن های داخلی مطابق روال جداول پارتیشن شده معمولی انجام می شود.
محدودیتها

محدودیت های مهم به شرح ذیل می باشد.

• تنها می توانیم از پارتیشنینگ LIST و RANGE استفاده نماییم.
• از پارتیشنینگ REFERENCE و SYSTEM پشتیبانی نمی کند.
• مطابق انتظار اغلب محدودیت های دیگر برای سایر انواع پارتیشنینگ می باشد.