بررسی قابلیت جدید Logical Partition Change Tracking (LPCT) برای Materialized Views در اوراکل ۲۳c

مقدمه :در پایگاه داده اوراکل نسخه ۲۳C قابلیت Logical Partition Change Tracking (LPCT) اجازه می دهد که MV هایی که از نظر داده ای Stale (کهنه) شده ­اند در سطح LPCT ها خود را به روز کنند.حال برای آشنایی بیشتر با این قابلیت در محیط آزمایشگاهی آن را مورد ارزیابی قرار می دهیم.ایجاد محیط:در ابتدا کاربر و جداول مورد نظر را ایجاد می کنیم.conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdbadrop user if exists testuser1 cascade;create user testuser1 identified by testuser1quota unlimited on users;grant db_developer_role to testuser1;grant select_catalog_role to testuser1;conn testuser1/testuser1@//localhost:1521/freepdb1در ادامه ۲ جدول مورد نیاز را ایجاد می کنیم. از این دو جدول برای ایجاد mv خود استفاده خواهیم کرد.drop table if exists sales purge;drop table if exists customers purge;create table customers (customer_id number primary key,age number,gender varchar2(1));insert into customers (customer_id, age, gender)values (1, 35, 'F'),(۲, ۵۴, 'M'),(۳, ۱۷, 'F'),(۴, ۱۵, 'M');commit;create table sales (id number generated always as identity primary key,sale_date date,product_id number,customer_id number,sale_value number(10,2),constraint sales_cust_fk foreign key (customer_id) references customers(customer_id));create index sales_cust_fk_i on sales(customer_id);insert into sales (sale_date, product_id, customer_id, sale_value)select to_date('2022','yyyy'),trunc(dbms_random.value(1,10)),trunc(dbms_random.value(1,5)),round(dbms_random.value(1,200),2)from dualconnect by level < 10001;commit;مقاله معرفی قابلیت Table values constructorایجاد پارتیشن های منطقی :پارتیشن های منطقی METADATA هستند نه پارتیشن های فیزیکی. آنها را می توان با جداول پارتیشن بندی شده یا غیر پارتیشن بندی شده استفاده کرد. تعاریف پارتیشن مشابه جداول پارتیشن بندی شده است.conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdbacreate logical partition tracking on testuser1.customerspartition by range (age) interval (20.5) (partition m0 values less than (20));create logical partition tracking on testuser1.salespartition by range (sale_date)interval(numtoyminterval(1, 'year')) (partition p0 values less than (to_date('01-01-2023', 'dd-mm-yyyy')),partition p1 values less than (to_date('01-01-2024', 'dd-mm-yyyy')));conn testuser1/testuser1@//localhost:1521/freepdb1ایجاد MV:ما MV ایجاد می کنیم تا فروش ها را بر اساس سن مشتری و تاریخ فروش جمع کنیم. توجه داشته باشید که ما هیچ materialized view refresh logs نداریم، اما همچنان می‌توانیم MV را با گزینه fast refresh ایجاد کنیم.drop materialized view if exists sales_mv;create materialized view sales_mvrefresh fastenable query rewriteasselect sum(s.sale_value) value_total,c.age,s.sale_datefrom sales s, customers cwhere s.customer_id = c.customer_idgroup by c.age, s.sale_date;exec dbms_stats.gather_schema_stats(null);view به نام USER_MVIEW_DETAIL_LOGICAL_PARTITION اطلاعات مربوط به پارتیشن های منطقی از جمله تازه بودن داده های موجود در آن را نمایش می دهد.set linesize 100column mview_name format a10column detailobj_owner format a15column detailobj_name format a15column dlp_name format a12column lpartname format a15column freshness format a10select mview_name,detailobj_owner,detailobj_name,detail_logical_partition_name as dlp_name,detail_logical_partition_number as dlp_no,freshness,last_refresh_timefrom user_mview_detail_logical_partitionorder by dlp_no;MVIEW_NAME DETAILOBJ_OWNER DETAILOBJ_NAME DLP_NAME DLP_NO FRESHNESS LAST_REFR---------- --------------- --------------- ------------ ---------- ---------- ---------SALES_MV TESTUSER1 SALES P0 0 FRESH 05-SEP-23SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23SALES_MV TESTUSER1 SALES P1 1 FRESH 05-SEP-23SQL>رویه DBMS_MVIEW.EXPLAIN_MVIEW به ما امکان می دهد وضعیت به روز رسانی MV تحقق یافته را بررسی کنیم. در میان سایر قابلیت ها، قابلیت های ردیابی پارتیشن منطقی (LPT) را نمایش می دهد.execute dbms_mview.explain_mview ('sales_mv');select capability_name, related_text, possiblefrom mv_capabilities_tablewhere mvname = 'SALES_MV'and capability_name like '%LPT%'order by 1, 2;CAPABILITY_NAME RELATED_TEXT POSSIBLE-------------------- --------------- ----------LPT YLPT_TABLE CUSTOMERS YLPT_TABLE SALES YLPT_TABLE_REWRITE CUSTOMERS YLPT_TABLE_REWRITE SALES YREFRESH_FAST_LPT YREWRITE_LPT Y۷ rows selected.SQLمی‌توانیم ببینیم LPT فعال است، MV از Fast Refresh با استفاده از LPT پشتیبانی می‌کند، و بازنویسی با استفاده از LPT برای ردیابی کهنگی پارتیشن‌های منطقی فعال است.به روز رسانی داده هایStale در MV با استفاده Logical Partition Change Tracking (LPCT):ما تعدادی داده جدید را در جداول SALES و CUSTOMERS وارد می کنیم.insert into customers (customer_id, age, gender)values (5, 25, 'M');insert into sales (sale_date, product_id, customer_id, sale_value)values (to_date('2023-10-01','yyyy-mm-dd'), 5, 5, 42);commit;MV به عنوان "NEEDS_COMPILE" علامت گذاری شده است.select stalenessfrom user_mviewswhere mview_name = 'SALES_MV';STALENESS-------------------NEEDS_COMPILESQL>ما view USER_MVIEW_DETAIL_LOGICAL_PARTITION را بررسی می‌کنیم تا تأثیر تغییر داده‌ها را ببینیم.set linesize 100column mview_name format a10column detailobj_owner format a15column detailobj_name format a15column dlp_name format a12column lpartname format a15column freshness format a10select mview_name,detailobj_owner,detailobj_name,detail_logical_partition_name as dlp_name,detail_logical_partition_number as dlp_no,freshness,last_refresh_timefrom user_mview_detail_logical_partitionorder by dlp_no;MVIEW_NAME DETAILOBJ_OWNER DETAILOBJ_NAME DLP_NAME DLP_NO FRESHNESS LAST_REFR---------- --------------- --------------- ------------ ---------- ---------- ---------SALES_MV TESTUSER1 SALES P0 0 FRESH 05-SEP-23SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23SALES_MV TESTUSER1 CUSTOMERS SYS_92702P1 1 STALE 05-SEP-23SALES_MV TESTUSER1 SALES P1 1 STALE 05-SEP-23SQL>جدول CUSTOMERS دارای یک پارتیشن منطقی جدید، ایجاد شده است که به عنوان staleعلامت گذاری شده است. پارتیشن منطقی P1 جدول SALES، که به داده های "۲۰۲۳" اشاره می کند، به عنوان stale علامت گذاری شده است.علیرغم stale بودن MV، همچنان می توانیم از آن برای بازنویسی پرس و جو استفاده کنیم، مشروط بر اینکه فقط پارتیشن های منطقی تازه را لمس کنیم.select sum(s.sale_value) value_total,c.age,s.sale_datefrom sales s, customers cwhere s.customer_id = c.customer_idand age = 35group by c.age, s.sale_date;select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID 48jg6r42js79d, child number 0-------------------------------------select sum(s.sale_value) value_total, c.age, s.sale_datefrom sales s, customers c where s.customer_id = c.customer_id andage = 35 group by c.age, s.sale_datePlan hash value: 1420257564-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| ۰ | SELECT STATEMENT | | | | 3 (100)| ||* ۱ | MAT_VIEW REWRITE ACCESS FULL| SALES_MV | 1 | 17 | 3 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------۱ - filter("SALES_MV"."AGE"=35)۲۰ rows selected.SQL>به روز رسانی با استفاده :Logical Partition Change Tracking (LPCT)هنگام فراخوانی رویه REFRESH در بسته DBMS_MVIEW، می‌توانیم با استفاده از گزینه "l" یا "L" یک بازخوانی سریع MV انجام دهیم. وقتی این کار را انجام می دهیم، می بینیم که پارتیشن های منطقی به عنوان Fresh علامت گذاری شده اند.execute dbms_mview.refresh('SALES_MV', 'L');select mview_name,detailobj_owner,detailobj_name,detail_logical_partition_name as dlp_name,detail_logical_partition_number as dlp_no,freshness,last_refresh_timefrom user_mview_detail_logical_partitionorder by dlp_no;MVIEW_NAME DETAILOBJ_OWNER DETAILOBJ_NAME DLP_NAME DLP_NO FRESHNESS LAST_REFR---------- --------------- --------------- ------------ ---------- ---------- ---------SALES_MV TESTUSER1 SALES P0 0 FRESH 05-SEP-23SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23SALES_MV TESTUSER1 CUSTOMERS SYS_92744P1 1 FRESH 05-SEP-23SALES_MV TESTUSER1 SALES P1 1 FRESH 05-SEP-23SQL>اطلاعات بیشتر :در اینجا اطلاعات بیشتری در مورد Logical Partition Change Tracking (LPCT) وجود دارد.LPCT را می توان با جداول پارتیشن بندی شده یا غیرپارتیشن بندی شده استفاده کرد.LPCT را می‌توان علاوه بر گزارش‌های مشاهده مادی‌شده استفاده کرد.از آنجایی که staleness در سطح پارتیشن منطقی ردیابی می شود، می توان به استفاده از بازنویسی پرس و جو ادامه داد، مشروط بر اینکه پرس و جو فقط به داده ها در پارتیشن های منطقی Fresh ارجاع دهد. بدون LPCT، MV روی جداول غیرپارتیشن بندی شده کاملاً stale یا کاملاً Fresh هستند. بنابراین LPCT granularity دقیق تری می دهد.LPCT نسبت به MV logs سبک‌تر است، زیرا هر ردیف تغییر یافته را ثبت نمی‌کند. در عوض آمار تغییرات را در سطح پارتیشن منطقی ردیابی می کند.جهت دریافت خدمات پایگاه داده اوراکل با ما در ارتباط باشیدمنبع مستند