جداول Private Temporary پایگاه‌داده اوراکل ۱۸c

مفهوم جداول Private Temporary در نسخه ۱۸c پایگاه‌داده اوراکل معرفی شده است. جداولی از نوع Temporary مبتنی بر حافظه (memory-based) که با توجه به تنظیمات مربوطه پس از پایان session یا transaction از بین می‌روند.

جداول Private Temporary پایگاه‌داده اوراکل ۱۸c

اگر شما از مدل‌های گوناگون پایگاه‌داده مانند SQL Server به پایگاه‌داده اوراکل مهاجرت نموده‌اید، ممکن است با ایده منحصربه‌فرد اوراکل در خصوص جداول Temporary نامأنوس باشید. در پایگاه‌داده SQL Server توسعه دهندگان عموماً از شیوه ایجاد یک جدول Temporary جهت اجرای عملیات و حذف جدول مذکور پس از اتمام عملیات استفاده می‌کنند. اوراکل برای این منظور از یک جدول Global Temporary Table (GTT) بهره می‌برد، این جدول از نوع permanent metadata object است و شامل سطرهایی در temporary segment ها می‌باشد که مبتنی بر session یا transaction هستند، و ایجاد و حذف مداوم GTT نیز امر متداولی نیست.
حال با معرفی جداول Private Temporary اوراکل نیز مشابه دیگر مدل‌های مختلف پایگاه‌داده از این قابلیت که علاوه‌بر اطلاعات، موجودیت جدول نیز از نوع Temporary باشد برخوردار می‌شود.

جداول Temporary
در پایگاه‌داده اوراکل از دو نوع جدول Temporary پشتیبانی می‌شود:
جداول Global Temporary: از نسخه ۸i به بعد پشتیبانی می‌شود.
جداول Private Temporary: از نسخه ۱۸c پشتیبانی می‌شود.

شرایط نام‌گذاری جداول Private Temporary
مقدار پارامتر اولیه PRIVATE_TEMP_TABLE_PREFIX که به صورت پیش‌فرض برابر “ORA$PTT_” است، معرف پیشوند لازم جهت نام‌گذاری جدول Private Temporary می‌باشد. در مثال زیر یک جدول Private Temporary بدون استفاده از شرایط نام‌گذاری فوق ایجاد و منتج به خطا شده است.

CREATE PRIVATE TEMPORARY TABLE my_temp_table (

id NUMBER,

description VARCHAR2(20)

);

CREATE PRIVATE TEMPORARY TABLE my_temp_table (

ERROR at line 1:

ORA-00903: invalid table name

SQL>

ایجاد جدول Private Temporary
در صورتی‌که در گذشته از جدول Global Temporary استفاده نموده باشید، ترکیب‌بندی ایجاد جدول Private Temporary برایتان آشنا خواهد بود.
عبارت ON COMMIT DROP DEFINITION به‌صورت پیش‌فرض نماینده آن است که جدول مذکور در پایان session یا transaction از بین می‌رود.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (

id NUMBER,

description VARCHAR2(20)

)

ON COMMIT DROP DEFINITION;

— Insert, but don’t commit, then check contents of PTT.

INSERT INTO ora$ptt_my_temp_table VALUES (1, ‘ONE’);

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

COUNT(*)

———-

SQL>

— Commit and check contents.

COMMIT;

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

SELECT COUNT(*) FROM ora$ptt_my_temp_table

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

همچنین عبارت ON COMMIT PRESERVE DEFINITION نماینده آن است که جدول و اطلاعات مربوطه می‌بایست فراتر از زمان پایان transaction ادامه یافته و تنها در پایان session از بین خواهد رفت.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (

id NUMBER,

description VARCHAR2(20)

)

ON COMMIT PRESERVE DEFINITION;

— Insert, but don’t commit, then check contents of PTT.

INSERT INTO ora$ptt_my_temp_table VALUES (1, ‘ONE’);

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

COUNT(*)

———-

SQL>

— Commit and check contents.

COMMIT;

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

COUNT(*)

———-

SQL>

— Reconnect and check contents of GTT.

CONN test/test@pdb1

SELECT COUNT(*) FROM ora$ptt_my_temp_table;

SELECT COUNT(*) FROM ora$ptt_my_temp_table

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

در مثال فوق اطلاعات جدول Private Temporary تا پس از commit نیز برقرار است اما بعد از قطع اتصال و ایجاد session جدید از بین خواهد رفت.
همچنین امکان ایجاد جدول Private Temporary با استفاده از روش CTAS نیز فراهم است.

CREATE PRIVATE TEMPORARY TABLE ora$ptt_emp AS

SELECT * FROM emp;

جدول Private Temporary و PL/SQL
همانطور که می‌دانید برای یک PL/SQL object امکان ارجاع مستقیم بهtemporary object که در زمان compile وجود خارجی ندارد، امکان‌پذیر نیست. در صورت نیاز به استفاده از یک جدول Private Temporary توسط یک Permanent object می‌بایست از شیوه dynamic SQL استفاده شود. در مثال زیر یک stored function که از یک جدول Private Temporary استفاده می‌نماید ایجاد شده است.

CREATE OR REPLACE FUNCTION ptt_test (p_id IN NUMBER)

RETURN VARCHAR2

l_sql VARCHAR2(32767);

l_return VARCHAR2(30);

BEGIN

l_sql := ‘CREATE PRIVATE TEMPORARY TABLE ora$ptt_my_temp_table (

id NUMBER,

description VARCHAR2(20)

)

ON COMMIT DROP DEFINITION’;

EXECUTE IMMEDIATE l_sql;

EXECUTE IMMEDIATE q'[INSERT INTO ora$ptt_my_temp_table VALUES (1, ‘ONE’)]’;

EXECUTE IMMEDIATE ‘SELECT description INTO :l_return FROM ora$ptt_my_temp_table WHERE id = :id’ INTO l_return USING p_id;

RETURN l_return;

END;

مشاهده می‌شود که function مذکور مطابق انتظار عمل می‌نماید.

SET SERVEROUTPUT ON

BEGIN

DBMS_OUTPUT.put_line(‘ptt_test(1) = ‘ || ptt_test(1));

END;

ptt_test(1) = ONE

PL/SQL procedure successfully completed.

SQL>

View ها
همانطور که پیش‌تر اشاره شد، جداول Private Temporary مبتنی بر حافظه (memory-based) هستند، بنابراین هیچ metadata از آن‌ها در data dictionary ثبت نمی‌شود. در نتیجه استفاده از USER_TABLES view جهت نمایش لیست جداول Private Temporary موجود در session امکان‌پذیر نیست.
لذا view های زیر به منظور نمایش اطلاعات جداول Private Temporary قابل استفاده می‌باشند:

• DBA_PRIVATE_TEMP_TABLES:

جهت نمایش تمام جداول Private Temporary موجود در پایگاه‌داده

• USER_PRIVATE_TEMP_TABLES:

جهت نمایش تمام جداول Private Temporary موجود در session کنونی

محدودیت‌ها
جداول Private Temporary تمام محدودیت‌های جداول Global Temporary را دارا می‌باشند، و محدودیت‎های دیگری نیز به شرح ذیل دارند:
• نام‌گذاری این نوع جداول باید با پیشوند مشخص شده در پارامتر PRIVATE_TEMP_TABLE_PREFIX آغاز شود که مقدار پیش‌فرض آن برابر “ORA$PTT_” است.
• امکان ارجاع مستقیم به جداول Private Temporary برای Permanent object ها وجود ندارد.
• ایندکس‌ها، materialized views و zone map ها بر روی جداول Private Temporary مجاز نیستند.
• کلیدهای اصلی (Primary key) و یا هر constraint که نیاز به index دارد، بر روی جداول Private Temporary مجاز نیستند.
• اختصاص مقادیر پیش‌فرض به ستون‌های جدول Private Temporary امکان‌پذیر نیست.
• جدول Private Temporary از طریق database link قابل دسترسی نیست.