Real-Time Materialized Views در اوراکل ۱۲٫۲

Real-Time Materialized Views در اوراکل ۱۲٫۲

۱ مقدمه
Materialized View یا به‌اختصار MV یکی از ویژگی‌های پرکاربرد پایگاه‌داده اوراکل است که به‌وسیله انجام پیش‌محاسبات سنگین ادغام جداول، کمک شایانی به افزایش کارایی پایگاه‌داده می‌کند. بدین ترتیب نرم‌افزارها و گزارش‌سازهایی که از ادغام جداول استفاده می‌کنند، مدت‌زمان کمتری را برای محاسبه و استخراج داده‌های موردنیاز منتظر پردازش‌های پایگاه‌داده منتظر می‌مانند. معضل اصلی این ویژگی تغییرات ایجاد شده در جداول اصلی، حدفاصل آخرین به‌روزرسانی و زمان فراخوانی جست‌وجو بر رویMV است. با استفاده از ویژگی Fast Refresh می‌توان تغییرات را در زمان اعمال شدن به MV نیز منتقل کرد. همچنین می‌توان از Trigger در زمان Commit بر روی جداول منبع استفاده کرد. بدیهی است که استفاده از هر یک از این دو روش، بار قابل‌توجهی را بر روی پایگاه‌داده ایجاد می‌کند.
در نگارش ۱۲.۲ پایگاه‌داده اوراکل، مفهوم Real Time Materialized View معرفی شده است که اجازه می‌دهد به‌صورت بخش بخش یک MV به‌روزرسانی نشده را به سمت جلو اصلاح کرد. در این مفهوم همانند Fast Refresh از رویدادهای MV استفاده می‌شود؛ با این تفاوت که تنها تغییرات جاری اعمال می‌شوند. ازآنجاکه رویدادها بر روی MV اعمال نمی‌شوند، در زمان‌هایی لازم است Fast Refresh انجام شود.

محدودیت‌های اصلی Real Time Materialized View به شرح زیر می‌باشند.
۱٫ الزاماً پارامتر QUERY_REWRITE_INTEGRITY باید مقدار پیش‌فرض ENFORCED و یا TRUSTED را داشته باشد. درصورتی‌که مقدار این پارامتر برابر STALE_TOLERATED باشد، امکان انجام تغییرات به سمت جلو میسر نخواهد شد.
۲٫ این ویژگی نمی‌تواند همراه با REFRESH … ON COMMIT استفاده شود.
۳٫ الزاماً MV باید در وضعیت ENABLE ON QUERY COMPUTATION باشد.
۴٫ به‌صورت پیش‌فرض برای جست‌وجوهایی که از MV داده‌ها را به‌صورت مستقیم فراخوانی می‌کنند، ویژگی Real Time Materialized View فعال نمی‌باشد. برای استفاده از این ویژگی باید از عبارت FRESH_MV در متن دستور جست‌وجو استفاده شود.

۲ پیاده‌سازی
در این بخش به بررسی چندین مثال پرداخته می‌شود. پیش‌فرض مثال‌های این بخش فراهم بودن یک پایگاه‌داده ۱۲.۲ به همراه یک pdb با نام pdb1 می‌باشد. به‌منظور انجام آزمایش‌ها، با استفاده از دستور زیر یک جدول آزمایشی به همراه داده‌های تصادفی ایجاد می‌شود. این دستور از طریق کاربری test در پایگاه‌داده pdb1 اجرا می‌شود.

SQL> CREATE TABLE order_lines

(

id NUMBER (10),

order_id NUMBER (10),

line_qty NUMBER (5),

total_value NUMBER (10, 2),

created_date DATE,

CONSTRAINT orders_pk PRIMARY KEY (id)

);

SQL> INSERT /*+ APPEND */

INTO order_lines

SELECT LEVEL AS id,

TRUNC (DBMS_RANDOM.VALUE (1, 1000)) AS order_id,

TRUNC (DBMS_RANDOM.VALUE (1, 20)) AS line_qty,

ROUND (DBMS_RANDOM.VALUE (1, 1000), 2) AS total_value,

TRUNC (SYSDATE – DBMS_RANDOM.VALUE (0, 366)) AS created_date

FROM DUAL

CONNECT BY LEVEL <= 100000;

SQL> COMMIT;

SQL> EXEC DBMS_STATS.gather_table_stats(USER, ‘order_lines’);

سپس پیش از ایجاد MV یک جدول برای نگهداری رویدادها ایجاد می‌شود.

SQL> DROP MATERIALIZED VIEW LOG ON order_lines;

SQL> CREATE MATERIALIZED VIEW LOG ON order_lines

WITH ROWID, SEQUENCE(order_id, line_qty, total_value)

INCLUDING NEW VALUES;

اکنون با استفاده از جدول منبع و همچنین جدول رویدادها می‌توان اقدام به ایجاد MV کرد. دستور زیر یک MV بر روی جدول order_lines ایجاد می‌کند. شایان ذکر است عبارت ENABLE ON QUERY COMPUTATION در نگارش ۱۲.۲ اضافه شده است و امکان Real Time Materialized View را فراهم می‌کند.

SQL> CREATE MATERIALIZED VIEW order_summary_rtmv

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

ENABLE ON QUERY COMPUTATION

SELECT order_id,

SUM(line_qty) AS sum_line_qty,

SUM(total_value) AS sum_total_value,

COUNT(*) AS row_count

FROM order_lines

GROUP BY order_id;

SQL> EXEC DBMS_STATS.gather_table_stats(USER, ‘order_summary_rtmv’);

۲-۱ Basic Rewrite
همان‌طور که در نگارش‌های پیشین اوراکل نیز این امکان وجود داشت؛ درصورتی‌که دستور جست‌وجو با استفاده از MV سریع‌تر اجرا گردد، اوراکل به‌صورت پیش‌فرض دستور جست‌وجو را بازنویسی می‌کند. به مثال زیر توجه شود.

SQL> SELECT order_id,

SUM (line_qty) AS sum_line_qty,

SUM (total_value) AS sum_total_value,

COUNT (*) AS row_count

FROM order_lines

WHERE order_id = 1

GROUP BY order_id;

ORDER_ID SUM_LINE_QTY SUM_TOTAL_VALUE ROW_COUNT

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

۱ ۱۰۲۶ ۴۵۵۹۳٫۲۸ ۱۰۲

۱ row selected.

در این مرحله یک جست‌وجو بر روی جدول منبع انجام شد. حال طرح اجرایی جست‌وجو بررسی می‌شود.

SQL> SELECT *

FROM DBMS_XPLAN.display_cursor();

PLAN_TABLE_OUTPUT

——————————————————————————————————————————————————————————————————–

SQL_ID 3rttkdd0ybtaw, child number 0

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

SELECT order_id, SUM(line_qty) AS sum_line_qty,

SUM(total_value) AS sum_total_value, COUNT(*) AS row_count FROM

order_lines WHERE order_id = 1 GROUP BY order_id

Plan hash value: 1165901663

—————————————————————————————————

—————————————————————————————————

| ۰ | SELECT STATEMENT | | | | 4 (100)| |

|* ۱ | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | 1 | 18 | 4 (0)| 00:00:01 |

—————————————————————————————————

Predicate Information (identified by operation id):

—————————————————

۱ – filter(“ORDER_SUMMARY_RTMV”.”ORDER_ID”=1)

۲۰ rows selected.

همان‌طور که مشاهده می‌شود، اوراکل به‌صورت پیش‌فرض جست‌وجو را بر روی MV بازنویسی و از MV برای فراخوانی داده‌ها استفاده کرده است.

۲-۲ Rewrite Plus Real-Time Refresh

اکنون یک سطر داده به جدول منبع اضافه می‌شود. بنابراین داده‌های MV و جدول منبع همسان نخواهند بود.

SQL> INSERT INTO order_lines

VALUES (100001,

۱,

۳۰,

۱۰۰۰۰,

SYSDATE);

۱ row created.

SQL> COMMIT;

Commit complete.

SQL> COLUMN mview_name FORMAT A30;

SQL> SELECT mview_name, staleness, on_query_computation FROM user_mviews;

MVIEW_NAME STALENESS O

—————————— ——————- –

ORDER_SUMMARY_RTMV NEEDS_COMPILE Y

همان‌طور که مشاهده می‌شود MV نیاز به به‌روزرسانی دارد. در شرایط کنونی برای یک MV معمولی مادامی‌که مقدار پارامتر QUERY_REWRITE_INTEGRITY در نشست جاری برابر STALE_TOLERATED نباشد، هیچ جست‌وجویی بازنویسی نمی‌شود؛ زیرا مقادیر MV متفاوت از جدول منبع می‌باشد. ازآنجایی‌که در زمان ایجاد این MV از عبارت ENABLE ON QUERY COMPUTATION استفاده شده است، این MV کماکان برای بازنویسی مورد استفاده قرار می‌گیرد؛ زیرا اوراکل به‌صورت خودکار اصلاحات را از جدول رویدادهای MV اعمال می‌کند.

SQL> SELECT order_id,

SUM(line_qty) AS sum_line_qty,

SUM(total_value) AS sum_total_value,

COUNT(*) AS row_count

FROM order_lines

WHERE order_id = 1

GROUP BY order_id;

ORDER_ID SUM_LINE_QTY SUM_TOTAL_VALUE ROW_COUNT

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

۱ ۱۰۵۶ ۵۵۵۹۳٫۲۸ ۱۰۳

SQL> SET LINESIZE 200 PAGESIZE 100 ;

SQL> SELECT *

FROM dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT

SQL_ID 3rttkdd0ybtaw, child number 1

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

SELECT order_id, SUM(line_qty) AS sum_line_qty,

SUM(total_value) AS sum_total_value, COUNT(*) AS row_count FROM

order_lines WHERE order_id = 1 GROUP BY order_id

Plan hash value: 1640379716

——————————————————————————————————————

——————————————————————————————————————

| ۰ | SELECT STATEMENT | | | | 19 (100)| |

| ۱ | VIEW | | 3 | 156 | 19 (27)| 00:00:01 |

| ۲ | UNION-ALL | | | | | |

|* ۳ | FILTER | | | | | |

|* ۴ | HASH JOIN OUTER | | 1 | 38 | 9 (23)| 00:00:01 |

|* ۵ | MAT_VIEW ACCESS FULL | ORDER_SUMMARY_RTMV | 1 | 22 | 4 (0)| 00:00:01 |

| ۶ | VIEW | | 1 | 16 | 5 (40)| 00:00:01 |

| ۷ | HASH GROUP BY | | 1 | 39 | 5 (40)| 00:00:01 |

| ۸ | VIEW | | 1 | 39 | 4 (25)| 00:00:01 |

|* ۱۰ | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |

| ۱۱ | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |

|* ۱۲ | TABLE ACCESS FULL | MLOG$_ORDER_LINES | 1 | 194 | 3 (0)| 00:00:01 |

| ۱۳ | VIEW | | 2 | 104 | 10 (30)| 00:00:01 |

| ۱۴ | UNION-ALL | | | | | |

|* ۱۵ | FILTER | | | | | |

| ۱۶ | NESTED LOOPS OUTER | | 1 | 95 | 4 (25)| 00:00:01 |

| ۱۷ | VIEW | | 1 | 78 | 4 (25)| 00:00:01 |

|* ۱۸ | FILTER | | | | | |

| ۱۹ | HASH GROUP BY | | 1 | 39 | 4 (25)| 00:00:01 |

|* ۲۰ | VIEW | | 1 | 39 | 4 (25)| 00:00:01 |

|* ۲۲ | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |

| ۲۳ | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |

|* ۲۴ | TABLE ACCESS FULL | MLOG$_ORDER_LINES | 1 | 194 | 3 (0)| 00:00:01 |

|* ۲۵ | INDEX UNIQUE SCAN | I_SNAP$_ORDER_SUMMARY_RTMV | 1 | 17 | 0 (0)| |

| ۲۶ | NESTED LOOPS | | 1 | 103 | 6 (34)| 00:00:01 |

| ۲۷ | VIEW | | 1 | 81 | 5 (40)| 00:00:01 |

| ۲۸ | HASH GROUP BY | | 1 | 39 | 5 (40)| 00:00:01 |

| ۲۹ | VIEW | | 1 | 39 | 4 (25)| 00:00:01 |

|* ۳۱ | VIEW | | 1 | 103 | 4 (25)| 00:00:01 |

| ۳۲ | WINDOW SORT | | 1 | 194 | 4 (25)| 00:00:01 |

|* ۳۳ | TABLE ACCESS FULL | MLOG$_ORDER_LINES | 1 | 194 | 3 (0)| 00:00:01 |

|* ۳۴ | MAT_VIEW ACCESS BY INDEX ROWID| ORDER_SUMMARY_RTMV | 1 | 22 | 1 (0)| 00:00:01 |

|* ۳۵ | INDEX UNIQUE SCAN | I_SNAP$_ORDER_SUMMARY_RTMV | 1 | | 0 (0)| |

——————————————————————————————————————

Predicate Information (identified by operation id):

—————————————————

۳ – filter(“AV$0″.”OJ_MARK” IS NULL)

۴ – access(SYS_OP_MAP_NONNULL(“ORDER_ID”)=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))

۵ – filter(“ORDER_SUMMARY_RTMV”.”ORDER_ID”=1)

۱۰ – filter(((“MAS$”.”OLD_NEW$$”=’N’ AND “MAS$”.”SEQ$$”=”MAS$”.”MAXSEQ$$”) OR

(INTERNAL_FUNCTION(“MAS$”.”OLD_NEW$$”) AND “MAS$”.”SEQ$$”=”MAS$”.”MINSEQ$$”)))

۱۲ – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-24 11:21:54’, ‘syyyy-mm-dd hh24:mi:ss’))

۱۵ – filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL)

۱۸ – filter(SUM(1)>0)

۲۰ – filter(“MAS$”.”ORDER_ID”=1)

۲۲ – filter(((“MAS$”.”OLD_NEW$$”=’N’ AND “MAS$”.”SEQ$$”=”MAS$”.”MAXSEQ$$”) OR

(INTERNAL_FUNCTION(“MAS$”.”OLD_NEW$$”) AND “MAS$”.”SEQ$$”=”MAS$”.”MINSEQ$$”)))

۲۴ – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-24 11:21:54’, ‘syyyy-mm-dd hh24:mi:ss’))

۲۵ – access(“ORDER_SUMMARY_RTMV”.”SYS_NC00005$”=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))

۳۱ – filter(((“MAS$”.”OLD_NEW$$”=’N’ AND “MAS$”.”SEQ$$”=”MAS$”.”MAXSEQ$$”) OR

(INTERNAL_FUNCTION(“MAS$”.”OLD_NEW$$”) AND “MAS$”.”SEQ$$”=”MAS$”.”MINSEQ$$”)))

۳۳ – filter(“MAS$”.”SNAPTIME$$”>TO_DATE(‘ 2017-07-24 11:21:54’, ‘syyyy-mm-dd hh24:mi:ss’))

۳۴ – filter((“ORDER_SUMMARY_RTMV”.”ORDER_ID”=1 AND “ORDER_SUMMARY_RTMV”.”ROW_COUNT”+”AV$0″.”D0″>0))

۳۵ – access(“ORDER_SUMMARY_RTMV”.”SYS_NC00005$”=SYS_OP_MAP_NONNULL(“AV$0”.”GB0″))

Result Cache Information (identified by operation id):

——————————————————

۹ –

۲۱ –

۳۰ –

Note

—–

– dynamic statistics used: dynamic sampling (level=2)

– this is an adaptive plan

۸۳ rows selected.

همان‌طور که مشاهده می‌شود تعداد سطرها برابر ۱۰۳ می‌باشد درصورتی‌که در جست‌وجوی پیشین مقدار ۱۰۲ سطر به‌عنوان کلیه سطرهای موجود بازگردانده شده بود. همچنین فعالیت‌های بیشتری به‌منظور حرکت به سمت جلو انجام شده است. در این مرحله اوراکل اصلاحات را از روی جدول رویدادها بر روی داده‌های فراخوانی شده اعمال کرده است.

۲-۳ Direct Query of Materialized View (FRESH_MV Hint)
علاوه بر بازنویسی جست‌وجو به‌وسیله اوراکل، می‌توان به‌صورت مستقیم از روی MV نیز داده‌ها را فراخوانی کرد.

SQL> SELECT order_id,

sum_line_qty,

sum_total_value,

row_count

FROM order_summary_rtmv

WHERE order_id = 1;

ORDER_ID SUM_LINE_QTY SUM_TOTAL_VALUE ROW_COUNT

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

۱ ۱۰۲۶ ۴۵۵۹۳٫۲۸ ۱۰۲

SQL> SET LINESIZE 200 PAGESIZE 100;

SQL> SELECT *

FROM dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT

SQL_ID 8tq6wzmccuzfm, child number 0

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

SELECT order_id, sum_line_qty, sum_total_value,

row_count FROM order_summary_rtmv WHERE order_id = 1

Plan hash value: 3344356712

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

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

| ۰ | SELECT STATEMENT | | | | 4 (100)| |

|* ۱ | MAT_VIEW ACCESS FULL| ORDER_SUMMARY_RTMV | 1 | 18 | 4 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

—————————————————

۱ – filter(“ORDER_ID”=1)

۱۹ rows selected.

همان‌طور که مشاهده می‌شود تعداد سطرها برابر ۱۰۲ محاسبه شده است. در این حالت داده‌ها به‌صورت مستقیم از روی MV فراخوانی شده‌اند و رویدادها بر روی نتیجه تأثیری نداشته‌اند. حال از عبارت FRESH_VM در جست‌وجو برای استفاده از ویژگی Real-Time Materialized View استفاده می‌شود.

SQL> SELECT /*+ FRESH_MV */

order_id,