همانطور که می دانید فراخوانی تابع در یک کوئری، context switch بین SQL engine و PL/SQL engine را در پی دارد این context switch به لحاظ پرفورمنسی اثر منفی را به دنبال خواهد داشت. برای مثال، دو دستور زیر خروجی یکسانی دارند اما مدت زمان اجرای آنها بسیار متفاوت است.

—اجرای دستور بدون استفاده از function:

SQL> select sum(sal) from(select salary+60 as sal from person);

  SUM(SAL)

———-

8891924480

Elapsed: 00:00:00.81

—اجرای دستور با استفاده از function:

SQL> select sum(sal) from(select sal_func(salary) as sal from person);

  SUM(SAL)

———-

8891924480

Elapsed: 00:00:10.91

توجه: متن تابع sal_func به صورت زیر است:

create or replace function sal_func(sal_param  number)

  return number

is

begin

  return sal_param +60 ;

end;

/

همانطور که مشاهده کردید اجرای دستور اول کمتر از یک ثانیه و اجرای پرس و جوی دوم، نزدیک به 11 ثانیه زمان برده است. این مقایسه ما را به سمت عدم استفاده از تابع در کوئری سوق می دهد اما در مواردی استفاده نکردن از تابع می تواند بسیار پرهزینه باشد بطوری که برای تغییر یک فرمول، ناگزیر باید قسمتهای مختلف برنامه را تغییر داد بنابرین عدم استفاده از function در کوئری، همیشه راه حل خوبی نیست!

اوراکل در نسخه 21c، برای حل مسئله پرفورمنسی یاد شده، SQL Macros را ارائه کرده است. با استفاده از این قابلیت، متن پرس و جوی حاوی function به فرم ساده و بدون استفاده از function بازنویسی می شود. برای مثال، اوراکل پرس و جوی

select sum(sal) from(select sal_func(salary) as sal from person);

را به کوئری زیر تبدیل می کند:

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT SUM(“PERSON”.”SALARY”+60) “SUM(SAL)” FROM “USEF”.”PERSON” “PERSON”

مطابق دسته بندی اوراکل، SQL Macros یکی از new featureهای نسخه 21c محسوب می شود هر چند که در داکیومنتهای اوراکل 20c معرفی شده و از نسخه 19.6 به بعد هم قابل استفاده است.

برای استفاده از قابلیت SQL Macros در یک تابع، باید در دستور ساخت آن تابع، از کلمه کلیدی SQL_MACRO استفاده شود و return type آن هم صرفا می تواند از نوع رشته(string، varchar2، clob و …) باشد همچنین مقدار برگشتی باید در عبارت ‘{}’q محصور شود.

برای مثال، در قسمت زیر، تابع sal_func را با استفاده از قابلیت SQL Macros بازنویسی کرده ایم:

create or replace function sal_func(sal_param number) return varchar2 sql_macro(scalar) is

begin

  return q'{

      sal_param +60

        }’;

end;

/

 

با تغییرات فوق در این تابع، زمان اجرای پرس و جوی استفاده شده در ابتدای متن، از 11 ثانیه به 1 ثانیه تغییر خواهد کرد:

SQL> select sum(sal) from(select sal_func(salary) as sal from person);

  SUM(SAL)

———-

8891924480

Elapsed: 00:00:01.11

SQL macroها بر دو نوع Table macros و Scalar macros هستند تابع Scalar macros در یک دستور sqlای، در قسمتهای SELECT list، WHERE clause، HAVING clause قرار می گیرد در صورتی که Table SQL macros در قسمت FROM clause استفاده می شود.

بنابرین تابع sal_func به عنوان یک Scalar SQL macro محسوب می شود و تابع زیر که حجیم ترین segment متعلق به یک اسکیما را برمی گرداند، نمونه ای از یک Table SQL macro می باشد:

create or replace function table_macro_Mesal(p_tab varchar2)

  return varchar2 sql_macro(table) is

begin

  return q'{

    select max(bytes/1024/1024) SIZE_MB from dba_segments d where owner =table_macro_Mesal.p_tab

   }’;

end;

/

SQL> select * from table_macro_Mesal(‘SYS’);

   SIZE_MB

———-

        46

همچنین تابع زیر، با دو پارامتر ورودی(تعداد(cnt) و نوع segment)، قرار است حجیم ترین سگمنتها را بر اساس نوع segment مشخص کند:

create or replace function table_macro_Mesal2(cnt number,typ varchar2)

  return varchar2 sql_macro(table) is

begin

  return q'{

 select owner, segment_name, bytes / 1024 / 1024 SIZE_MB

   from dba_segments

   where segment_type=table_macro_Mesal2.typ

  order by bytes desc fetch first cnt rows only

   }’;

end;

/

SQL> select * from table_macro_Mesal2(5,’TABLE’);

OWNER      SEGMENT_NAME                 SIZE_MB

———- ————————- ———-

USEF       JADVAL_TEST                      312

USEF       PERSON                           104

SYS        IDL_UB2$                          16

SYS        WRI$_SQLSET_PLAN_LINES            15

SYS        IDL_UB1$                          12