اوراکل 26ai – امکان استفاده از bind variable با قابلیت Materialized View Query Rewrite

تا قبل از نسخه 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 استفاده شده است.