تا قبل از نسخه 26ai، کوئری‌هایی که شامل bind variable بودند، نمی‌توانستند از قابلیت query rewrite با استفاده از materialized view (MV) بهره ببرند و اوراکل مجبور بود داده‌ها را مستقیماً از جدول‌های پایه بخواند، که باعث از دست رفتن مزایای کاراییِ داده‌های پیش‌تجمیع‌شده (pre-aggregated data) می‌شد.

از Oracle AI Database 26ai به بعد، این محدودیت حذف شده است و این بهبود به optimizer اجازه می‌دهد مقدار واقعی bind variable را در زمان اجرا بررسی کرده و تصمیم بگیرد که آیا کوئری می‌تواند با استفاده از MV بازنویسی شود یا خیر.

مقایسه رفتار در اوراکل 19c و 26ai

ایجاد Materialized View

SQL> CREATE MATERIALIZED VIEW vahid.mv_emp_dep
ENABLE QUERY REWRITE AS
SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<100
GROUP BY department_id;

Materialized view created

این MV داده‌های تجمیع‌شدهٔ کارمندان را برای دپارتمان‌هایی با شناسهٔ کمتر از ۱۰۰ ذخیره کرده و برای query rewrite خودکار مناسب است.

بررسی Query rewrite در 19c

وقتی کوئری‌ای اجرا کنیم که کاملاً در دامنهٔ تعریف MV باشد (مثل department_id < 90)،اوراکل 19c آن را به‌درستی با MV بازنویسی می‌کند:

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<90
GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3470845597
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     8 |    80 |     2   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_EMP_DEP |     8 |    80 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("MV_EMP_DEP"."DEPARTMENT_ID"<90)

اما اگر شرط کوئری خارج از دامنهٔ MV باشد (مانند department_id > 90)، اوراکل مجبور می‌شود از جدول پایه بخواند.

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id>90
GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     8 |   208 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |     8 |   208 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |     8 |   208 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPARTMENT_ID">90)

مشکل اصلی: Bind Variable

وقتی از bind variable استفاده کنیم:

variable depid number
exec :depid := 90;

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<:depid
GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |    22 |   572 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |           |    22 |   572 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |    22 |   572 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------

حتی اگر مقدار bind variable (یعنی ۹۰) در دامنهٔ MV باشد، اوراکل 19c قادر به query rewrite نیست، زیرا optimizer در هنگام parsing مقدار depid: را نمی‌داند. به همین دلیل، از MV صرفنظر می شود.

بررسی Query rewrite در Oracle AI Database 26ai

در اوراکل 26ai این محدودیت از بین رفته و اگر مقدار bind variable در دامنهٔ MV قرار داشت، کوئری بازنویسی می شود.

برای کوئری دارای bind variable:

SQL> variable depid number
SQL> exec :depid:=90
PL/SQL procedure successfully completed.

SQL> SELECT department_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary
FROM vahid.employees where department_id<:depid
GROUP BY department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3470845597
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     8 |    80 |     3   (0)| 00:00:01 |
|*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_EMP_DEP |     8 |    80 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("MV_EMP_DEP"."DEPARTMENT_ID"<90)

در اوراکل 26ai، مقدار bind در زمان اجرا بررسی شده و چون در محدودهٔ MV است (<100)، optimizer کوئری را با استفاده از MV بازنویسی می‌کند. Execution plan در متن اصلی نشان داده شده و تأیید می‌کند که از MAT_VIEW REWRITE ACCESS استفاده شده است.

Vahid Yousefzadeh

ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com