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

مقدمه :

در پایگاه داده اوراکل نسخه ۲۳C قابلیت Logical Partition Change Tracking (LPCT) اجازه می دهد که MV هایی که از نظر داده ای Stale (کهنه) شده اند در سطح LPCT ها خود را به روز کنند.

حال برای آشنایی بیشتر با این قابلیت در محیط آزمایشگاهی آن را مورد ارزیابی قرار می دهیم.

ایجاد محیط:

در ابتدا کاربر و جداول مورد نظر را ایجاد می کنیم.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

drop user if exists testuser1 cascade;

create user testuser1 identified by testuser1

quota 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 dual

connect by level < 10001;

commit;

مقاله معرفی قابلیت Table values constructor

ایجاد پارتیشن های منطقی :

پارتیشن های منطقی METADATA هستند نه پارتیشن های فیزیکی. آنها را می توان با جداول پارتیشن بندی شده یا غیر پارتیشن بندی شده استفاده کرد. تعاریف پارتیشن مشابه جداول پارتیشن بندی شده است.

conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba

create logical partition tracking on testuser1.customers

partition by range (age) interval (20.5) (

partition m0 values less than (20)

);

create logical partition tracking on testuser1.sales

partition 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_mv

refresh fast

enable query rewrite

select sum(s.sale_value) value_total,

c.age,

s.sale_date

from sales s, customers c

where s.customer_id = c.customer_id

group by c.age, s.sale_date;

exec dbms_stats.gather_schema_stats(null);

view به نام USER_MVIEW_DETAIL_LOGICAL_PARTITION اطلاعات مربوط به پارتیشن های منطقی از جمله تازه بودن داده های موجود در آن را نمایش می دهد.

set linesize 100

column mview_name format a10

column detailobj_owner format a15

column detailobj_name format a15

column dlp_name format a12

column lpartname format a15

column freshness format a10

select mview_name,

detailobj_owner,

detailobj_name,

detail_logical_partition_name as dlp_name,

detail_logical_partition_number as dlp_no,

freshness,

last_refresh_time

from user_mview_detail_logical_partition

order 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-23

SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23

SALES_MV TESTUSER1 SALES P1 1 FRESH 05-SEP-23

SQL>

رویه DBMS_MVIEW.EXPLAIN_MVIEW به ما امکان می دهد وضعیت به روز رسانی MV تحقق یافته را بررسی کنیم. در میان سایر قابلیت ها، قابلیت های ردیابی پارتیشن منطقی (LPT) را نمایش می دهد.

execute dbms_mview.explain_mview (‘sales_mv’);

select capability_name, related_text, possible

from mv_capabilities_table

where mvname = ‘SALES_MV’

and capability_name like ‘%LPT%’

order by 1, 2;

CAPABILITY_NAME RELATED_TEXT POSSIBLE

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

LPT Y

LPT_TABLE CUSTOMERS Y

LPT_TABLE SALES Y

LPT_TABLE_REWRITE CUSTOMERS Y

LPT_TABLE_REWRITE SALES Y

REFRESH_FAST_LPT Y

REWRITE_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 staleness

from user_mviews

where mview_name = ‘SALES_MV’;

STALENESS

——————-

NEEDS_COMPILE

SQL>

ما view USER_MVIEW_DETAIL_LOGICAL_PARTITION را بررسی می‌کنیم تا تأثیر تغییر داده‌ها را ببینیم.

set linesize 100

column mview_name format a10

column detailobj_owner format a15

column detailobj_name format a15

column dlp_name format a12

column lpartname format a15

column freshness format a10

select mview_name,

detailobj_owner,

detailobj_name,

detail_logical_partition_name as dlp_name,

detail_logical_partition_number as dlp_no,

freshness,

last_refresh_time

from user_mview_detail_logical_partition

order 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-23

SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23

SALES_MV TESTUSER1 CUSTOMERS SYS_92702P1 1 STALE 05-SEP-23

SALES_MV TESTUSER1 SALES P1 1 STALE 05-SEP-23

SQL>

جدول CUSTOMERS دارای یک پارتیشن منطقی جدید، ایجاد شده است که به عنوان staleعلامت گذاری شده است. پارتیشن منطقی P1 جدول SALES، که به داده های “۲۰۲۳” اشاره می کند، به عنوان stale علامت گذاری شده است.

علیرغم stale بودن MV، همچنان می توانیم از آن برای بازنویسی پرس و جو استفاده کنیم، مشروط بر اینکه فقط پارتیشن های منطقی تازه را لمس کنیم.

select sum(s.sale_value) value_total,

c.age,

s.sale_date

from sales s, customers c

where s.customer_id = c.customer_id

and age = 35

group 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_date

from sales s, customers c where s.customer_id = c.customer_id and

age = 35 group by c.age, s.sale_date

Plan hash value: 1420257564

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

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

| ۰ | 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_time

from user_mview_detail_logical_partition

order 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-23

SALES_MV TESTUSER1 CUSTOMERS M0 0 FRESH 05-SEP-23

SALES_MV TESTUSER1 CUSTOMERS SYS_92744P1 1 FRESH 05-SEP-23

SALES_MV TESTUSER1 SALES P1 1 FRESH 05-SEP-23

SQL>

اطلاعات بیشتر :

در اینجا اطلاعات بیشتری در مورد Logical Partition Change Tracking (LPCT) وجود دارد.

LPCT را می توان با جداول پارتیشن بندی شده یا غیرپارتیشن بندی شده استفاده کرد.
LPCT را می‌توان علاوه بر گزارش‌های مشاهده مادی‌شده استفاده کرد.
از آنجایی که staleness در سطح پارتیشن منطقی ردیابی می شود، می توان به استفاده از بازنویسی پرس و جو ادامه داد، مشروط بر اینکه پرس و جو فقط به داده ها در پارتیشن های منطقی Fresh ارجاع دهد. بدون LPCT، MV روی جداول غیرپارتیشن بندی شده کاملاً stale یا کاملاً Fresh هستند. بنابراین LPCT granularity دقیق تری می دهد.
LPCT نسبت به MV logs سبک‌تر است، زیرا هر ردیف تغییر یافته را ثبت نمی‌کند. در عوض آمار تغییرات را در سطح پارتیشن منطقی ردیابی می کند.

جهت دریافت خدمات پایگاه داده اوراکل با ما در ارتباط باشید

منبع مستند