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

SQL>  select /*+INDEX(tbl1,IND_CODE)*/ count(*) from tbl1 where code=2 ;

  COUNT(*)

———-

  51199980

Elapsed: 00:00:05.23

همانطور که می بینید، این پرس و جو حدودا در زمان 4 ثانیه اجرا شده است با برداشتن HINTای که در این پرس  و جو موجود است، آن را مجددا اجرا می کنیم:

SQL> select count(*) from tbl1 where code=2 ;

  COUNT(*)

———-

  51199980

Elapsed: 00:00:01.60

مشاهده می کنید که زمان اجرای پرس و جو با برداشتن HINT از 5 ثانیه به 1 ثانیه کاهش پیدا کرده است قصد داریم با هر بار اجرای پرس و جوی اول، دیتابیس به صورت خودکار پرس و جوی دوم(که فاقد HINT است) را اجرا کند به عبارتی دیگر، دیتابیس در پس زمینه query rewrite را انجام دهد.

مشابه این کار را قبلا با کمک sql profile انجام دادیم و در این متن قصد داریم query rewrite را از طریق بسته DBMS_ADVANCED_REWRITE انجام دهیم که به این جهت لازم است از پروسیجر declare_rewrite_equivalence استفاده کنیم:

SQL> BEGIN

  2  sys.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (

  3  name             => ‘Q1’,

  4  source_stmt      => ‘select /*+INDEX(tbl1,IND_CODE)*/ count(*) from usef.tbl1 where code=2’,

  5  destination_stmt => ‘select count(*) from usef.tbl1 where code=2’,

  6  validate         => FALSE,

  7  rewrite_mode     => ‘GENERAL’);

  8  END;

  9  /

ORA-30394: source statement identical to the destination statement

مشاهده می کنید که دو کوئری، متن یکسانی دارند(صرف نظر از قسمت HINT) از اینرو اجرای بسته DBMS_ADVANCED_REWRITE با خطا مواجه شده است برای جلوگیری از رخ دادن این خطا، عبارت 1=1 را به انتهای دستور دوم اضافه می کنیم:

SQL> BEGIN

  2  sys.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence (

  3  name             => ‘Q1’,

  4  source_stmt      => ‘select /*+INDEX(tbl1,IND_CODE)*/ count(*) from usef.tbl1 where code=2’,

  5  destination_stmt => ‘select count(*) from usef.tbl1 where code=2 and 1=1’,

  6  validate         => FALSE,

  7  rewrite_mode     => ‘GENERAL’);

  8  END;

  9  /

PL/SQL procedure successfully completed

برای انجام بازنویسی کوئری توسط اوراکل، باید پارامتر query_rewrite_integrity(در سطح session و یا system) به trusted تنظیم شود:

SQL> ALTER SESSION SET query_rewrite_integrity = trusted;

Session altered.

پس از تنظیم پارامتر query_rewrite_integrity، در همین session پرس و جو را اجرا می کنیم:

SQL> select /*+INDEX(tbl1,IND_CODE)*/ count(*) from usef.tbl1 where code=2;

  COUNT(*)

———-

  51199980

Executed in 1.535 seconds

همانطور که می بینید، با کمک بسته DBMS_ADVANCED_REWRITE، در زمان اجرای کوئری از ایندکس استفاده نشده و زمان اجرای ان به 1 ثانیه کاهش یافته است.

برای حذف Q1 می توان دستور زیر اجرا کرد:

SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => ‘Q1’);

PL/SQL procedure successfully completed