می دانیم که اطلاعات جداول از نوع external، در خارج از محیط دیتابیس و در قالب فایلی در سیستم عامل ذخیره می شوند. بسیار روشن است که اگر حجم فایل حاوی دیتا از حد مشخصی بیشتر شود، عملیات جستجو با سرعت کندتری انجام خواهد شد.

اوراکل در نسخه 12c قابلیت پارتیشن بندی external table را ارائه کرده است که میتواند در سرعت دستیابی به اطلاعات این نوع از جداول بهبودی را ایجاد کند. در ادامه این متن، به بررسی این قابلیت خواهیم پرداخت.

در نظر بگیرید که اطلاعات افراد به تفکیک هر استان در قالب فایلی مجزا در محیط سیستم عامل ذخیره شده اند:

استان سمنان:

[oracle@ol6 ~]$ vi semnan.txt

1,hasan,rohani,semnan,09142223333

2,mahmood,ahmadi nejad,semnan,09145553333

…..

[oracle@ol6 ~]$ cat semnan.txt |wc -l

30000003

[oracle@ol6 ~]$ ls -lh semnan.txt

-rw-r—r–. 1 oracle oinstall 973M Dec 21 11:47 semnan.txt

استان یزد:

[oracle@ol6 ~]$ vi yazd.txt

3,mohammad,khatami,yazd,09175554444

4,mohammad taghi,mesbah yazdi,yazd,09175554411

…..

[oracle@ol6 ~]$ ls -lh yazd.txt

-rw-r–r–. 1 oracle oinstall 719M Dec 21 11:56 yazd.txt

[oracle@ol6 ~]$ wc -l yazd.txt

20941002 

استان بویراحمد:

[oracle@ol6 ~]$ vi boyrahmat.txt

5,rahmat,rabbani,boyrahmat,09105214545

[oracle@ol6 ~]$ ls -lh  boyrahmat.txt

-rw-r–r–. 1 oracle oinstall 39 Dec 21 12:08 boyrahmat.txt

[oracle@ol6 ~]$ wc -l boyrahmat.txt

1 

همانطور که می بینید، فایل مربوط به استان سمنان حدودا 30 میلیون رکورد دارد و همچنین یزد حدودا 20 میلیون رکورد و بویراحمد هم صرفا یک رکورد دارد. جدولی با نام myexttbl را بر اساس ساختار این سه فایل ایجاد می کنیم:

SQL> create table myexttbl  (

   id        number,

   name      varchar2(20),

   last_name varchar2(20),

   ostan     varchar2(20),

   mobile    number(12)

) organization external (

   type oracle_loader

   default directory mydir

   access parameters (

      records delimited by newline

      fields terminated by ‘,’

      missing field values are null

      reject rows with all null fields

   )

   location (‘semnan.txt’, ‘yazd.txt’)

);

حال قصد داریم تعداد رکوردهای دو استان بویراحمد و سمنان را با کمک دستور sqlای زیر مشخص کنیم:

SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on;

SQL>  select count(*) from myexttbl where ostan=’boyrahmat’;

  COUNT(*)

———-

         1

Elapsed: 00:01:44.50

SQL> alter system flush buffer_cache;

System altered.

SQL> select count(*) from myexttbl where ostan=’semnan’;

  COUNT(*)

———-

  30000003

Elapsed: 00:01:32.50

همانطور که می بینید، زمان محاسبه تعداد رکوردهای استان سمنان با 30 میلیون رکورد مشابه است با استان بویراحمد که صرفا یک رکورد دارد!!! همچنین هر دو execution plan یکسانی دارند:

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYEXTTBL’);

PL/SQL procedure successfully completed

SQL> set autotrace traceonly explain

SQL> set linesize 100

SQL>  select count(*) from myexttbl where ostan=’semnan’;

Plan Hash Value : 328480286

Predicate Information (identified by operation id):
________________________________________
• 2 – filter(“OSTAN”=’semnan’)

SQL> select count(*) from myexttbl where ostan=’boyrahmat’;
Plan Hash Value : 328480286

Predicate Information (identified by operation id):

2 – filter(“OSTAN”=’boyrahmat’)

همانطور که در ابتدای این متن هم بیان شد، در نسحه 12cR2، اوراکل قابلیت پارتیشن بندی را برای جداول از نوع external ارائه کرد که بر اساس ان می توان هر فایل را به عنوان یک پارتیشن مجزا در نظر گرفت:

create table myexttbl  (

   id        number,

   name      varchar2(20),

   last_name varchar2(20),

   ostan     varchar2(20),

   mobile    number(12)

) organization external (

   type oracle_loader

   default directory mydir

   access parameters (

      records delimited by newline

      fields terminated by ‘,’

      missing field values are null

      reject rows with all null fields

   )

   )

partition by list (ostan)

(

   partition sales_ext_part_amer values (‘yazd’) location (‘yazd.txt’)

 , partition sales_ext_part_asoc values (‘semnan’) location (‘semnan.txt’)

 , partition sales_ext_part_emea values (‘boyrahmat’) location (‘boyrahmat.txt’)

) ;

با این تغییرات، دستورات را مجددا اجرا کرده و سرعت اجرای دستورات را با زمانی که جدول پارتیشن نشده بود، مقایسه می کنیم:

SQL> startup force;

Pluggable Database opened.

SQL> set timing on;

SQL> select count(*) from usef.myexttbl where ostan=’boyrahmat’;

  COUNT(*)

———-

         1

Elapsed: 00:00:04.06

SQL>  startup force;

Pluggable Database opened.

SQL>  select count(*) from usef.myexttbl where ostan=’semnan’;

  COUNT(*)

———-

  30000002

Elapsed: 00:00:35.82

همانطور که می بینید، با تغییرات انجام شده، تعداد رکوردهای استان بویراحمد صرفا در 4 ثانیه محاسبه شد و این زمان برای استان سمنان در 35 ثانیه انجام شد. در قسمت زیر، execution plan دستور را مشاهده می کنید:

SQL> exec dbms_stats.gather_table_stats(ownname => ‘USEF’,tabname => ‘MYEXTTBL’);

PL/SQL procedure successfully completed

SQL> select count(*) from myexttbl where ostan=’boyrahmat’;

Plan Hash Value : 3927328017

SQL> select count(*) from myexttbl where ostan=’semnan’;
Plan Hash Value : 3927328017